Forum Discussion

Mahmoud_nabir's avatar
Mahmoud_nabir
Frequent Visitor
7 years ago

Date Table with weekday Table

i have created date table and want to find the sum of planned visits during selected date

 

 

 

Date Table  Visits by WeekDay
DateWeekDay  visitdayplanned_visits
4/1/2018Sunday  Friday290
4/2/2018Monday  Monday1351
4/3/2018Tuesday  Saturday1411
4/4/2018Wednesday  Sunday1399
4/5/2018Thursday  Thursday1344
4/6/2018Friday  Tuesday1409
4/7/2018Saturday  Wednesday1363
4/8/2018Sunday    
4/9/2018Monday    

 

when select date from 4/1/2018  to 4/9/2018

 

i  need planned visit result as below

 

 

1399 + 1351 + 1344 +1363+ 1409+290+1411+ 1399 +1351

 

as i need sum planned visits without distinct by Visit day.

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Mahmoud_nabir

     

    I hope you have 2 tables Date Table, Visit by day table.

     

    1. Make sure you form the relationship between these 2 tables using the Date.Weekday= VisitbyDay.VisitDay columns , it should be M:1 ( Many end of date, 1 end of Visitday).

     

    2. Pull the date ( from data table ) and SUM(visitbyday0 Planned_Visits])  into your visual, you will get the desired result.

     

    I hope this is what you are looking for.Let me know if you face any issues.

     

    Thanks
    Raj

    • Mahmoud_nabir's avatar
      Mahmoud_nabir
      Frequent Visitor
      7 years ago

      Dear rajendran

       

      thanks for your replay.

       

      unfortunately it doesn't work as it result the planned visits for one week only.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hi Mahmoud_nabir

     

    Try the following

     

    a) Set a relationship between the Weekday of Date Table and VisitDay of VisitsPlanned table.

    b) Set the relationship to Many to 1 and Crossfilter direction both

    c)  Create a measure PlannedVistits= SUM([planned_Visits])

    d) Create a slicer for Date from DateTable

    e) Create a table visual with values as - Date from Date Table, Weekday from DateTable and measure [PlannedVisits]

    f) You should get your result.

    g) you could also show the [PlannedVisits] as a card visual.

     

    Sample Screen Shot

     

    If it works for you please accpet it as solution and also give KUDOS.

     

    Cheers

     

    CheenuSing