calculation within a chart question

[alec1982]

hi all,

I have the following table

Store     year     Projected Amount

1          2015     1000

2         2016     1500

3         2017     800

4         2017     1200

5         2018     900

I need to build a bar chart of sum of future projected amount by year.. so 2015 will show everything in all years. 2016 will show the sum of 2016 and 2017 and 2017 will show only its value..doable?

Best,

Alec

[#2]

Please check below document.

Calculating rolling n-period totals, averages or other aggregations

[alec1982]

hi Sinan,

While your logic is 100% right it will not help me..

I didnt provide the right example but here is the right one. the desired chart will show the values per year as I explained but the user needs to have the ability to filter on store...

Store     year     Projected Amount

1          2015     1000

2         2016     1500

3         2017     800

4         2017     1200

5         2018     900

[Kushal_Chawda]

You can do something like this

Data:

LOAD * Inline [

Store,    year,    Projected Amount

1,          2015 ,    1000

2 ,        2016  ,  1500

3 ,        2017  ,  800

4 ,        2017  ,  1200

5 ,        2018  ,  900 ];

ProjectAmount:

LOAD * Inline [

Jnk ];

FOR i=1 to FieldValueCount('year');

LET vYear = FieldValue('year',$(i));

Concatenate(ProjectAmount)

LOAD sum([Projected Amount]) as Project_Amount,

'$(vYear)' as year

Resident Data

where year >= '$(vYear)';

NEXT

DROP Field Jnk;

Please see the attached

[MarcoWedel · Accepted solution]

Hi,

another solution could be a calculated dimension using the valueloop() function to create the year and an expression like

Sum( If(year>=valueloop(min(year),max(year)),[projected amount]))

Hope this helps

Regards

Marco

[Kushal_Chawda]

Hi MarcoWedel

I think this expression is returning NULL values when I use Year as dimension. May be I am doing something wrong, can you tell me how it works

[alec1982]

hi Kush,

this is close but not giving the right amounts.. if you filter on stores 3 ... you get the values of 2900 while the values should be 800...

[alec1982]

hi Dathu,

interesting document but that is the opposite of what I am trying to do..

[sinanozdemir]

Hopefully, this comes close to what you are looking for:

When you filter on a store number, it keeps the original projected amount:

I am also attaching the qvw.

Also, here is the expression for folks who have free edition of QlikView:

Hope this helps.

[alec1982]

thank you.. this one helped but I needed to put min and max year into variables in order to get it to work...

[MarcoWedel]

Try the valueloop function as calculated dimension instead of year.

Regards

Marco