topic Re: Sum range for a selected year in QlikView

Sum range for a selected year

Tue, 02 Jun 2015 09:04:35 GMT

Hello world,

If I want to sum the range of months for a selected year, and I cannot use year-to-date? Is it possible

For example if I select jun 2013

I want to se jan.. jun 2013 (From January to Jun)

I try to do it like the YTD and the master calendar, but it will not work, because I only have old dates - not dates in 2015.

How can we do this without using the current year flags?

Would it be with a selected year flag or something like that?

Regards Martha

Re: Sum range for a selected year

swuehl — Tue, 02 Jun 2015 10:04:47 GMT

You can try something like

=Only(aggr(rangesum(above( sum({1}Value),0,rowno())),Year,Month))

This will only show a value, when you select a YearMonth, like Jun 2013.

You need to take care that load order of Year and Month is chronologic.

Instead of {1} set identifier, you can only clear all calendar fields, so further selections will be considered in the aggregation.

Re: Sum range for a selected year

Tue, 02 Jun 2015 10:13:25 GMT

Hi Swuehl,

Thank you very much for your answer.

I am testing it - it only gives me a value. I don't quite understand the {1}

I am trying to analize it

Re: Sum range for a selected year

swuehl — Tue, 02 Jun 2015 10:20:15 GMT

You can also go for a script based solution:

Calculating rolling n-period totals, averages or other aggregations

The set identifier {1} is part of set analysis skills. With set analysis, you can modify user selections (here I just ignored any user selection in the inner aggregation).

Re: Sum range for a selected year

Tue, 02 Jun 2015 11:23:46 GMT

Yes I can see. I will try it with {$}

Thanks for you answer.

Re: Sum range for a selected year

swuehl — Tue, 02 Jun 2015 11:26:35 GMT

{$} will not be enough, because it will just apply the user selection, limiting the sum() to Jun 2013 again.

At least you should clear selections on the calendar fields:

=Only(aggr(rangesum(above( sum({$<Month=, Year=, YearMonth= >}Value),0,rowno())),Year,Month))

Re: Sum range for a selected year

Tue, 02 Jun 2015 11:34:29 GMT

But I can see that aggregating a range would work if we have the months as dimensions.

Without any other dimension.

Because what it does is: sum the rows..

Re: Sum range for a selected year

Tue, 02 Jun 2015 11:38:39 GMT

But actually helps me a lot - thanks swuehl

Regards

Martha

Re: Sum range for a selected year

Tue, 02 Jun 2015 11:46:52 GMT

Kindly attach some sample file which illustrates what you need actually...

it will help all of us to solve the issue

Re: Sum range for a selected year

Tue, 02 Jun 2015 12:42:05 GMT

you mean you selected year 2014 and month jul..? and the above is the output..?

Re: Sum range for a selected year

Tue, 02 Jun 2015 12:42:45 GMT

Sorry Jun

Re: Sum range for a selected year

Tue, 02 Jun 2015 13:10:54 GMT

I will set the picture again. If I select Year='2014' the result would be this.

If I also select Month = {'Jun'} then it would only show from jan to jun

Re: Sum range for a selected year

Tue, 02 Jun 2015 13:21:23 GMT

check my sample file

Re: Sum range for a selected year

Tue, 02 Jun 2015 13:24:37 GMT

I cannot open it in my personal edition..

Would you paste the code please?

Thanks in advance

Martha

Re: Sum range for a selected year

Tue, 02 Jun 2015 13:28:43 GMT

Hi check the output. if this is what you wanted i will post the code

Re: Sum range for a selected year

Tue, 02 Jun 2015 13:32:10 GMT

Exactly, that is what I am trying to do

Can you paste the code?

Re: Sum range for a selected year

Tue, 02 Jun 2015 13:36:30 GMT

use the attached csv file

and paste this script

LOAD Year,

Month,

Amount,

'YTD'as Flag

FROM

data.csv.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)Where Month = 1;

For i = 2 to 12;

Concatenate

LOAD Year,

$(i) as Month,

'YTD'as Flag,

Sum(Amount) as Amount

FROM

data.csv.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)Where Month <= $(i)

Group by Year,

$(i) ,

'YTD';

NEXT i

Concatenate

LOAD Year,

Month,

Amount ,

'Monthly'as Flag

FROM

data.csv.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

and at the front end

use

two expression

Dimension = Month

Expressions are

1.stand alone :Sum({<Month={"<=$(=max(Month))"},Flag={'Monthly'}>}Amount)

2.Cumulative : Sum({<Month={"<=$(=max(Month))"},Flag={'YTD'}>}Amount)

Re: Sum range for a selected year

Tue, 02 Jun 2015 13:57:58 GMT

All right - I can see that it is very smart, because one actually does a sum from the load. I can apply this to my file.

Thanks again Veeki