Last week to Date

[stephenedberkg]

sunindia‌

Each upload week has 12 forcast weeks.

each id has lock periods

in dashboad im using only Upload calendar.

If i select Upload week 1  in the calendar which has 12 forcast week ,

and it has two possible ids 1 and 2 which has different lock period.

if id 1 has selected

now i want to calculate

forecast week 1 + id l has lock period 1 = 2

so i want to display sales for the forecast week  2

=Sum({<Year_UP={$(=Max(Year_UP))},Week_UP= {$(=Max(Week_UP))},Year_UP=,Week_UP=>} Aggr(If(Only({1}Week_F) = Max(TOTAL <ID> Week_UP ) + Only(TOTAL <ID> [lock period])  ,

Sum({<Year_UP={$(=Max(Year_UP))},Week_UP= {$(=Max(Week_UP))},Year_UP=,Week_UP=>}sales)), ID, Week_F ))

this is Current week expression its working correctly but last week expression is not working correctly.

PFA !

Regards

Stephen G

[kaushiknsolanki]

Hi,

What is the output you are looking for, because I saw from your data that for the selection you have made, the current week value is 21, which is right as per you and previous value is 10, which is also right, and you are getting both value correctly.

Regards,

Kaushik Solanki

[stephenedberkg]

Thanks for your reply

previous week in the sense it should take upload week

if i select the upload week 2 the answer is 4 for previous week the answer is should be 96.

this is my expected output

[kaushiknsolanki]

Hi,

Can you help me to understand how you got value 96.

Regards,

Kaushik Solanki

[stephenedberkg]

Dear Kaushik

Sorry itseems there is some mistake in sample

please find this sample

if i select the upload week 2 the answer is 5 for previous week the answer is should be 26 !.

[kaushiknsolanki · Accepted solution]

Hi,

Use this expression.

=Sum({<Year_UP={$(=Max(Year_UP))},Week_UP= {$(=Max(Week_UP)-1)}>} Aggr(If(Only({1}Week_F) = (Max({<Year_UP={$(=Max(Year_UP))},Week_UP= {$(=Max(Week_UP)-1)}>}TOTAL <ID> Week_UP)) + Only({<Year_UP={$(=Max(Year_UP))},Week_UP= {$(=Max(Week_UP)-1)}>}TOTAL <ID> [lock period])  ,

Sum({<Year_UP={$(=Max(Year_UP))},Week_UP= {$(=Max(Week_UP)-1)}>}sales)), ID, Week_F ))

Regards,

Kaushik Solanki

[stephenedberkg]

Dear Kaushik

awesome..

i am getting correct values.

how to write this expression into  YTD and MTD

[kaushiknsolanki]

Hi,

You can change the set analysis to take MTD ot YTD values.

Regards,

Kaushik Solanki

[stephenedberkg]

=Sum({<Year_UP={$(=Max(Year_UP))},MonthID_UP= {$(=Max(MonthID_UP))}>}

Aggr(If(Only({1}MonthIDS_F) = (Max({<Year_UP={$(=Max(Year_UP))},MonthID_UP= {$(=Max(MonthID_UP))}>}TOTAL <ID> MonthID_UP))

+ Only({<Year_UP={$(=Max(Year_UP))},MonthID_UP= {$(=Max(MonthID_UP))}>}TOTAL <ID> [lock period])  ,

Sum({<Year_UP={$(=Max(Year_UP))},Week_UP= {$(=Max(Week_UP))}>}sales)), ID, MonthIDS_F ))

i tried this for MTD but its not working correct.

[sunny_talwar]

For YTD you can try this:

=Sum({<Year_UP={$(=Max(Year_UP))},Week_UP= {'<=$(=Max(Week_UP))'}>} Aggr(If(Only({1}Week_F) = (Max({<Year_UP={$(=Max(Year_UP))},Week_UP= {'<=$(=Max(Week_UP))'}>}TOTAL <ID> Week_UP)) + Only({<Year_UP={$(=Max(Year_UP))},Week_UP= {'<=$(=Max(Week_UP))'}>}TOTAL <ID> [lock period])  ,

Sum({<Year_UP={$(=Max(Year_UP))},Week_UP= {'<=$(=Max(Week_UP))'}>}sales)), ID, Week_F))