topic Re: Last week to Date in QlikView

Last week to Date

stephenedberkg — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Last week to Date

kaushiknsolanki — Mon, 07 Mar 2016 06:29:41 GMT

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

Re: Last week to Date

stephenedberkg — Mon, 07 Mar 2016 06:39:32 GMT

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

Re: Last week to Date

kaushiknsolanki — Mon, 07 Mar 2016 06:45:30 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Last week to Date

stephenedberkg — Mon, 07 Mar 2016 06:55:04 GMT

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 !.

Re: Last week to Date

kaushiknsolanki — Mon, 07 Mar 2016 07:18:05 GMT

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

Re: Last week to Date

stephenedberkg — Mon, 07 Mar 2016 07:36:10 GMT

Dear Kaushik

awesome..

i am getting correct values.

how to write this expression into YTD and MTD

Re: Last week to Date

kaushiknsolanki — Mon, 07 Mar 2016 07:40:06 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Last week to Date

stephenedberkg — Mon, 07 Mar 2016 08:07:19 GMT

=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.

Re: Last week to Date

sunny_talwar — Mon, 07 Mar 2016 10:30:07 GMT

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))

Re: Last week to Date

sunny_talwar — Mon, 07 Mar 2016 10:39:27 GMT

For MTD (assuming a week will never overlap between months)

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

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