topic Re: Set analysis in QlikView

Set analysis

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

Hi,

can anyone explain the following set analysis:

sum({<[%Month Num]={"<$(=num(Month(Today())))"},[Year Diff]={'-2'}>}[Sales])

It's supposed to calculate the sum from the beginning of the year until privies month (meaning from Januar until December 2018 )

This set analysis worked fine until last month, meaning it calculated the sales from January until November, but now it's not working. how can I fix it?

Month	%Month Num	Year	Year Diff
Jan	1	2019	-1
Feb	2	2019	-1
Mar	3	2019	-1
Apr	4	2019	-1
May	5	2019	-1
Jun	6	2019	-1
Jul	7	2019	-1
Aug	8	2019	-1
Sep	9	2019	-1
Oct	10	2019	-1
Nov	11	2019	-1
Dec	12	2019	-1
Jan	1	2018	-2
Feb	2	2018	-2
Mar	3	2018	-2
Apr	4	2018	-2
May	5	2018	-2
Jun	6	2018	-2
Jul	7	2018	-2
Aug	8	2018	-2
Sep	9	2018	-2
Oct	10	2018	-2
Nov	11	2018	-2
Dec	12	2018	-2

Re: Set analysis

Kushal_Chawda — Sun, 05 Jan 2020 18:57:17 GMT

I think today() function used in set analysis won't work correctly as it will always check the latest date. Instead you can use below

=sum({<[%Month Num],Year, Month,[%Month Num]={"<$(=max([%Month Num]))"},[Year Diff]={'-2'}>}[Sales])

If this is still not working please explain your end goal so that we can propose alternative solution

Re: Set analysis

bellesol — Sun, 05 Jan 2020 19:29:11 GMT

That not exactly what I need,

I want to calculate the sum of sales from the beginning of the last two years until the previous month of that year.

for example, if today is December, I would like to calculate the sum from January 2018 until November 2018.

so, today is January then I would like to calculate the sum from January 2018 until December 2018.

If I understand currently, the set analysis that you send, it will always calculate the sum of sales from January 2018 until December,Regardless of the month we are in

Re: Set analysis

Kushal_Chawda — Sun, 05 Jan 2020 19:40:40 GMT

Let's take current year scenario. If today is Jan 2020 then what should your data give you? From Jan 2019 to Dec 2019

Let's say if you are in June 2020 then what should your data give you?

Re: Set analysis

bellesol — Sun, 05 Jan 2020 19:42:38 GMT

yes, If today is Jan 2020, the data should give from Jan 2019 to Dec 2019

Re: Set analysis

Kushal_Chawda — Sun, 05 Jan 2020 19:44:07 GMT

what about second scenario?

Re: Set analysis

bellesol — Sun, 05 Jan 2020 19:46:00 GMT

Jan 2020 until May 2020

Re: Set analysis

bellesol — Sun, 05 Jan 2020 20:10:49 GMT

I think something like this:

If (num(Month(Today()))=1,
sum({<[Year Diff]={'-2'} >}[Sales]),
sum({<[%Month Num]={"<$(=num(Month(Today())))"},[Year Diff]={'-2'} >}Sales]))

sum({<[%Month Num]={">=1"},[Month Diff]={"<=-1"},[Year Diff]={'-2'}>}[Sales])

But I'm not sure that's the right way

Re: Set analysis

Kushal_Chawda — Sun, 05 Jan 2020 21:54:37 GMT

Do you want it to work dynamically. For eg. let's say you selected Year 2018 and Month Jan than data should display from Jan 2017 to Dec 2017. If you selected Year 2018 and Month June then data should display, Jan 2018 to May 2018?

Re: Set analysis

Taoufiq_Zarra — Sun, 05 Jan 2020 22:07:02 GMT

Hi,

if I understand correctly

you want to calculate the sum of sales from the beginning of the last two years until the previous month of that year

so for beginning of the last two years :

=MakeDate(year(today())-2,1,1)

and for previous month of that year :

=MakeDate(year(today())-2,Month(AddMonths(today(),-1)),1)

I'm guessing you have the sale's date , assuming it's [SalesDate]

so the solution may be:

=sum({<SalesDate= {">=$(=MakeDate(year(today())-2,1,1))<=$(=MakeDate(year(today())-2,Month(AddMonths(today(),-1)),1))"}> } Sales)

Re: Set analysis

Brett_Bleess — Tue, 21 Jan 2020 21:26:47 GMT

Hey Bellosol, did you get things working or are you still stuck? Just wanted to be sure you circled back if something someone posted did help to mark that post using the Accept as Solution so they get credit and the other Community folks know what worked.

I was thinking of something someone posted recently related to things at year end, but I cannot recall what the setting was he mentioned to change the behavior, as this is almost what things sound like. I just remembered what it was:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/NumberInterpretationVariables/BrokenWeeks.htm

Maybe that will be of some help potentially if you are still working on things.

Regards,
Brett