topic Re: Last Years same week day in QlikView

Last Years same week day

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

Hello again everyone,

I have a pivot table with dimension the stores and expressions the daily quantity,month to date quanity, and year to date quantity.

The user selects the date from a listbox DD/MM/YYYY

What I want to achieve is to split the store's row into 3 subrows which the first will show current quantity ,the 2nd the perviou's year's same day quantity and the 3rd the comparisson of 1st and 2nd row.

I tried to calculate last years quantity but the result is the same as the current year.

The set analysis I use for the previous year is : Sum({$< trn_date={"=$(=Date(AddYears(max(trn_date),-1)))"}>} qty)

trn_date is the date field from which the user selects one date ,qty is the quantity.

What is the mistake I make? Please suggest a solution

Thank you in advance

Re: Last Years same week day

kostiskampouris — Wed, 06 Dec 2017 11:01:51 GMT

Re: Last Years same week day

sunny_talwar — Wed, 06 Dec 2017 12:08:32 GMT

Would you be able to share a sample to look at this?

Re: Last Years same week day

kostiskampouris — Wed, 06 Dec 2017 14:43:28 GMT

Here is a sample, and this is what i want to achieve is in the excel file.

Thank you

Re: Last Years same week day

sunny_talwar — Wed, 06 Dec 2017 14:54:15 GMT

Not sure how you got 2016 numbers? I don't see 12/04/2016... but you have 35 for that date? But if the data is correct, this should work

=Pick(islandperiod,

Sum({<trn_date = {"$(=AddMonths(Max(trn_date), -12))"}>}qty),

Sum({<trn_date = {">=$(=MonthStart(Max(trn_date), -12)) <=$(=AddMonths(Max(trn_date), -12))"}>} qty),

Sum({<trn_date = {">=$(=YearStart(Max(trn_date), -1)) <=$(=AddMonths(Max(trn_date), -12))"}>} qty)

)

Re: Last Years same week day

kostiskampouris — Wed, 06 Dec 2017 15:19:16 GMT

This works fine for the month to date and year to date but for the exact same day last year returns 0 no matter which is the selected day.

Re: Last Years same week day

sunny_talwar — Wed, 06 Dec 2017 15:31:39 GMT

Try this

=Pick(islandperiod,

Sum({<trn_date = {"$(=TimeStamp(AddMonths(Max(trn_date), -12), 'YYYY-MM-DD hh:mm:ss.fff'))"}>} qty),

Sum({<trn_date = {">=$(=MonthStart(Max(trn_date), -12)) <=$(=AddMonths(Max(trn_date), -12))"}>} qty),

Sum({<trn_date = {">=$(=YearStart(Max(trn_date), -1)) <=$(=AddMonths(Max(trn_date), -12))"}>} qty)

)

Re: Last Years same week day

kostiskampouris — Wed, 06 Dec 2017 15:41:44 GMT

Perfect!Thank you so much