Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Hello everyone! I hope you are okay all of you!
and I need your help. I have a dashboard with expressions like sum({<Year={'2015'}>}[Units sold]) and
I need to do a cumulative version of those expressions please help me.
thank you.
Best,
Ab
thank you Mr. Singh for you help but I found the answer
sum({<Year={'2015'}>}[Units sold]) :
this is the expression for period it means that if you select 1 month it will give you only the number of units sold in that month.
But if you use this expression bellow , if you select one month it will give you the sum of units sold in that month plus the amount of previous months.
=sum({1<Year={$(#=ONLY(Year))},Month={"<=$(=MAX(Month))"}>}
aggr(
sum({<Year={'2015'}>}[Units sold])
)
,Artikel,Year,Month))
Can you explain a bit more about "Cumulative version", what you mean by it? An example will be great if added.
if you have an expression like this: sum({<Year={'2015'}>}[Units sold]),
and a list box of months, when you do select 01 or January the expression give you the number of units sold in January. if you do select April or 04 it gives you number of units sold in April, But in Cumulative when you do select April it gives you the number of units sold from January to April
In that case you can use below 2 ways:
1) Add MonthNum as a field and use that in set analysis. Get the current selected MonthNum into a variable and then use like
MonthNum = {">=1<=vMonthSelected"}
2) If you have a Date field on which the Calendar is created, then you can take the MonthEnd date of current selected Month into a variable and for starting you can either use a hardcoded date or use YearStart function : then use like:
DateField = {">=vStartDate<=vEndDate"}
or you can search the threads with Rolling Months and will find plenty of info.
Hope this is helpful.
Thanks,
Singh
thank you Mr. Singh for you help but I found the answer
sum({<Year={'2015'}>}[Units sold]) :
this is the expression for period it means that if you select 1 month it will give you only the number of units sold in that month.
But if you use this expression bellow , if you select one month it will give you the sum of units sold in that month plus the amount of previous months.
=sum({1<Year={$(#=ONLY(Year))},Month={"<=$(=MAX(Month))"}>}
aggr(
sum({<Year={'2015'}>}[Units sold])
)
,Artikel,Year,Month))
thank you Mr. Singh for you help but I found the answer
sum({<Year={'2015'}>}[Units sold]) :
this is the expression for period it means that if you select 1 month it will give you only the number of units sold in that month.
But if you use this expression bellow , if you select one month it will give you the sum of units sold in that month plus the amount of previous months.
=sum({1<Year={$(#=ONLY(Year))},Month={"<=$(=MAX(Month))"}>}
aggr(
sum({<Year={'2015'}>}[Units sold])
)
,Artikel,Year,Month))