topic Re: Cumulative totals in QlikView

Cumulative totals

Anonymous — Tue, 26 May 2015 09:00:47 GMT

Hi,

I have a year field which contains 100years(1915 to 2015) of data. if I select one year like 2013 it has to show the count(Contracts) for 2013,2012,2011,2010(ie last 4years of selected year) in line chart with cumulative totals.(ie 2013 shows sum(contracts ) from 1915 to 2013 and 2012 shows sum(contracts) from 1915 to 2012,...).

Thanks in advance

Best Regards

Anusha K

Re: Cumulative totals

maxgro — Tue, 26 May 2015 09:17:44 GMT

try here

https://community.qlikview.com/docs/DOC-4252

Re: Cumulative totals

Anonymous — Tue, 26 May 2015 10:22:56 GMT

How to display selected year and last 3 years of selected year data in line chart

Re: Cumulative totals

Anonymous — Tue, 26 May 2015 10:26:03 GMT

Here year is a dimension for line chart and count(contracts) is expression

Re: Cumulative totals

Anonymous — Tue, 26 May 2015 10:29:36 GMT

Anusha,

Can you attach a sample application after reducing the data.

Thanks

BKC

Re: Cumulative totals

sunny_talwar — Tue, 26 May 2015 10:30:50 GMT

I was gonna ask the same thing

Re: Cumulative totals

Anonymous — Tue, 26 May 2015 10:33:47 GMT

Hi,

You can try this expression

Count({<Year={'>$(=max(Year)-3)<=$(=max(Year))'}>}Contracts)

Regards,

Greeshma

Re: Cumulative totals

john_s_w_wood — Tue, 26 May 2015 12:23:18 GMT

If the accumulate function on the expression tab doesnt work for you you may nee to use the rangesum,above and rowno functions thus

rangesum(above(count(contracts),0,rowno()))

this should accumulate for you. Ill send an example shortly

Re: Cumulative totals

john_s_w_wood — Tue, 26 May 2015 12:29:01 GMT

In the example attached, above is used to look at previous records. the format for the whole statement is

rangesum(above(aggregate(data), to how many rows above 0 = current row, how many rows back to start from which is given by rowno() which takes the accumulation back to the first row))

Re: Cumulative totals

Anonymous — Thu, 28 May 2015 09:25:47 GMT

@ !Hi Team,

Sorry for my late response.

Please check the below file.In this The expression shows all the years cumulative totals .when i select on year filter it has to show last 3 years cumulative totals data like if i select 2014 it has to show 2014, 2013,2012 with cumulative totals.In line chart like 2014-500,2013-350,2012-220

Please help me to fild solution for this it is an urgent requirement in my project.

Thanks in advance

Best Regards

Anusha k

Re: Cumulative totals

Anonymous — Thu, 28 May 2015 09:29:57 GMT

In my application i have 100 years of data so,accumulation function won't work for this.I have to show totals from 1915 to 2015 and 1915 to 2014 and 1915 to 2013 like this in line chart.If i select 2002 it has to show last 4years data in line chart like 2002,2001,2000,1999 with cumulatiive totals like 1915 to 2002 ,1915 to 2001,1915 to 2000 etc..

Re: Cumulative totals

Anonymous — Thu, 28 May 2015 09:41:29 GMT

Hi,

Please see attached. You can create a Cumulative Sales column in the script using peek function.

Regards,

Greeshma

Re: Cumulative totals

Anonymous — Thu, 28 May 2015 10:08:03 GMT

Its Excellent greeshma .Exactly meet my requirement. Thank you very much .I've to check this in my application.

Re: Cumulative totals

Anonymous — Thu, 28 May 2015 11:22:21 GMT

Hi Greeshma I have checked my application with the solution provided by you, But in my scenario I have to cumulate count of distinct id's.

I have modified your script according to that but it is not giving me the expected results( It is not cumulating the id's).

Can you please look into this...

Thanks,

Anusha.

Re: Cumulative totals

Anonymous — Thu, 28 May 2015 11:25:14 GMT

Can you provide some sample data and expected result to work on?

Re: Cumulative totals

Anonymous — Thu, 28 May 2015 11:46:51 GMT

Hi Greeshma,

Please find the attached file

Re: Cumulative totals

Anonymous — Thu, 28 May 2015 12:10:03 GMT

Hi Anusha,

Is this what you are looking for? If not, please provide expected output.

Regards,

Greeshma