topic Re: Set analysis compare with same period last year in QlikView

Set analysis compare with same period last year

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

Hi,

I want to sum last years sales for the same period as selected for this year and have the following expression:

=sum({<Datefield= {">=$(=Addmonths(min(Datefield)),-12)<=$(=Addmonths(max(Datefield)),-12)"}> } Sales)

For example, if selected dates are 20190101, 20190201 and 20190301 I want to sum sales between 20180101 and 20180301.

Date field format is 'YYYYYMMDD' and I have one date per month to select from: 20190101, 20190201 and so on.

I can't figure out why the expression is not working, and would be very thankful for any help.

Kind regards, Hilde

Re: Set analysis compare with same period last year

lironbaram — Sat, 01 Jun 2019 18:07:40 GMT

you need to make sure

that the function inside the set analysis returns the date in the same format as your date field

so try adding date around your function and format it

Re: Set analysis compare with same period last year

Vegar — Sat, 01 Jun 2019 18:31:22 GMT

To get addmonths() to work your Datefield needs to be recognized as a date. If you use =Addmonths(MAX(Datefield),-12)
as an expression in your application it should return the correct corresponding date.

Are you making selections in other period fields such as Year, month etc.? If so you will need to cancel out their effect like this: =sum({<Year, Month, Datefield= {">=$(=Addmonths(min(Datefield)),-12)<=$(=Addmonths(max(Datefield)),-12)"}> } Sales)

Re: Set analysis compare with same period last year

Vegar — Sat, 01 Jun 2019 18:33:32 GMT

Oh, I noticed that your parenthesis are a bit off in the addmonths() function. See adjusted expression below.

=sum({<Datefield= {">=$(=Addmonths(min(Datefield),-12))<=$(=Addmonths(max(Datefield),-12))"}> } Sales)