Set analysis

[bellesol]

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

[Kushal_Chawda]

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

[bellesol]

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

[Kushal_Chawda]

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?

[bellesol]

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

[Kushal_Chawda]

what about second scenario?

[bellesol]

Jan 2020 until May 2020

[bellesol]

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

OR

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

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

[Kushal_Chawda]

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?

[Taoufiq_Zarra]

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)