Sum range for a selected year

[#1]

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

[#2]

Sorry Jun

[#3]

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

[#4]

check my sample file

[#5]

I cannot open it in my personal edition..

Would you paste the code please?

Thanks in advance

Martha

[#6 · Accepted solution]

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

[#7]

Exactly, that is what I am trying to do

Can you paste the code?

[#8]

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)

[#9]

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