Set Analysis ,weekly format

[VishalSingh18]

Hello

I am almost done with this dashboard and the only problem that exists is with filter of weekend dates , in filter ifi select a single week date the table for previous 4 weeks go null/blank if i select 2 weeks in filter it shows only max(week)-1, and so on

what I want to achieve is when i select any date in filter it should dynamically show the previous 4 weeks of data

in the below attached image i have filtered 4 week of data from max  date and it shows only 3 weeks in table while it should also show the 4th previous week dynamically

Regards

Vishal Singh

[HirisH_V7]

You should put on select trigger in such a way that, it should select last 4 weeks from what ever date you select.

For making it easy , we can have year and weeknumber field. 

[VishalSingh18]

this value is further divided in location,  and weekend date are required as rows while location in columns

[HirisH_V7]

As locations are synced with dates if i m not wrong, they will appear accordingly.

If we are missing anything in b/w. provide some sample data with u r result expected based on selection. so we can look forward to achieve that.

[VishalSingh18]

I HAVE ATTAHED SAMPLE

AND ALSO REFER THE IMAGE ATTACHED IN START OF DISCUSSION

[HirisH_V7 · Accepted solution]

Hi check this,

LOAD Location, 
     Actual_RPP, 
     Planned_RPP, 
     Weekly_Rev_Actual, 
     Weekly_Revenue_Planned, 
     RPP_LB_Actual, 
     RPP_LB_Planned, 
     Plan_Date, 
          'W.E: ' & Weekend(Num#(Plan_Date,'DD-MM-YY')) as w.e.Date, 
          Year(Num#(Date#(Plan_Date,'DD-MM-YY')))&Week(Num#(Date#(Plan_Date,'DD-MM-YY'))) as w.e.Date_Num,
     M_Date, 
     week_No, 
     floor, 
     L
FROM
[Service(AutoRecovered).xlsx]
(ooxml, embedded labels, table is RPP);

UI Set analysis:

Sum({1<w.e.Date_Num = {">=$(=Max(w.e.Date_Num-3)) <= $(=Max(w.e.Date_Num))"}>} Planned_RPP)

PFA

[VishalSingh18]

unable to open the file

just help me with dimensions and expression

Thanks

[HirisH_V7]

Dimensions are left field and top field, Expression is ..

=Sum({1<w.e.Date_Num = {">=$(=Max(w.e.Date_Num-3)) <= $(=Max(w.e.Date_Num))"}>} Planned_RPP)