Last Years same week day

[kostiskampouris]

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

[kostiskampouris]

[sunny_talwar]

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

[kostiskampouris]

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

Thank you

[sunny_talwar]

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)

)

[kostiskampouris]

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.

[sunny_talwar · Accepted solution]

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)

)

[kostiskampouris]

Perfect!Thank you so much