topic Re: calculation within a chart question in QlikView

calculation within a chart question

alec1982 — Thu, 20 Aug 2015 16:17:48 GMT

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

Re: calculation within a chart question

sunny_talwar — Thu, 20 Aug 2015 16:31:00 GMT

May be like this:

If(Year = 2015, Sum(Total [Projected Amount]),

If(Year = 2016, Sum(Total {<Year = {'2016', '2017'}>} [Projected Amount]),

If(Year = 2017, Sum(Total {<Year = {'2017'}>} [Projected Amount]))))

Re: calculation within a chart question

alec1982 — Thu, 20 Aug 2015 16:32:48 GMT

thank you for your quirk reply... I am looking for more dynamic solution rather than hard code the years...

Re: calculation within a chart question

sinanozdemir — Thu, 20 Aug 2015 16:37:08 GMT

Can you replace the hard coded values with Min(Year) and Max(Year)?

So If(Year = Min(Year), Sum(Total [Projected Amount]),

If(Year = Max(Year), Sum(Total {<Year = {$(=Max(Year))}> [Projected Amount]}, and so on...

Re: calculation within a chart question

Oleg_Troyansky — Thu, 20 Aug 2015 16:39:39 GMT

Hi Alec,

There is a number of ways to do it - some more scalable than others. The most scalable solution is to create a so called "As of Date" table and associate your "display" year with the "forecast" year. This way, 2015 can be associated with 2015, 2016, 2017 etc...

You can find a number of free sources describing how to create the "As of Date" table, including my blog:

QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

It's also described in detail in my new book "QlikView Your Business".

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: calculation within a chart question

kkkumar82 — Thu, 20 Aug 2015 16:41:00 GMT

Hi please find attached qvw , it may be helpful.

Thanks

Kiran Kumar

Re: calculation within a chart question

alec1982 — Thu, 20 Aug 2015 17:00:08 GMT

thank you all.. I need to stay away from hard coding the years.. the above sample is not the actual data and we dont know the actual years.

Re: calculation within a chart question

sinanozdemir — Thu, 20 Aug 2015 17:16:27 GMT

Not a great solution, but this can be optimized better:

This gives you the below data model:

Thanks

Re: calculation within a chart question

alec1982 — Thu, 20 Aug 2015 17:38:37 GMT

thank you Sinan, Can you send me the qvw..

Re: calculation within a chart question

sinanozdemir — Thu, 20 Aug 2015 17:42:38 GMT

Sure. Please see the attached.

Hope it helps.

Thanks

Re: calculation within a chart question

Thu, 20 Aug 2015 17:49:22 GMT

Please check below document.

Calculating rolling n-period totals, averages or other aggregations

Re: calculation within a chart question

alec1982 — Thu, 20 Aug 2015 18:16:05 GMT

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

Re: calculation within a chart question

Kushal_Chawda — Thu, 20 Aug 2015 18:47:15 GMT

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

DROP Field Jnk;

Please see the attached

Re: calculation within a chart question

MarcoWedel — Thu, 20 Aug 2015 18:50:44 GMT

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

Re: calculation within a chart question

Kushal_Chawda — Thu, 20 Aug 2015 19:00:18 GMT

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

Re: calculation within a chart question

alec1982 — Thu, 20 Aug 2015 20:19:18 GMT

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

Re: calculation within a chart question

alec1982 — Thu, 20 Aug 2015 20:23:18 GMT

hi Dathu,

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

Re: calculation within a chart question

sinanozdemir — Thu, 20 Aug 2015 21:01:58 GMT

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.

Re: calculation within a chart question

alec1982 — Thu, 20 Aug 2015 21:19:35 GMT

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

Re: calculation within a chart question

MarcoWedel — Thu, 20 Aug 2015 21:33:34 GMT

Try the valueloop function as calculated dimension instead of year.

Regards

Marco