topic Re: Cumulative expressions in QlikView

Cumulative expressions

Tue, 12 May 2015 12:18:42 GMT

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,

Re: Cumulative expressions

Tue, 12 May 2015 12:28:41 GMT

Can you explain a bit more about "Cumulative version", what you mean by it? An example will be great if added.

Re: Cumulative expressions

Tue, 12 May 2015 12:39:22 GMT

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

Re: Cumulative expressions

Tue, 12 May 2015 12:45:35 GMT

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

Re: Cumulative expressions

Thu, 14 May 2015 12:17:43 GMT

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

Re: Cumulative expressions

Thu, 14 May 2015 12:18:51 GMT

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