topic Re: Help with CONCAT and Valuelist in a Waterfall Chart in QlikView

Help with CONCAT and Valuelist in a Waterfall Chart

darrellbutler — Mon, 17 Aug 2015 10:59:27 GMT

Hi Folks,

I need to create a waterfall chart that shows the differences between different forecast positions. The chart must show the two forecast positions plus the differences in between across a drill down group.

I've created the two forecast positions, but the difference by brand and region is not calculating correctly as its replicating the overall difference across all dimensions.

I've attached a dummy qvw that simulates my real life issue !

Thanks in advance

Re: Help with CONCAT and Valuelist in a Waterfall Chart

Tue, 18 Aug 2015 06:32:05 GMT

Your parameter eBrandDim will never = Budget or Forecast as it's set up to only be "Brand" or "Region", which means the expression will always default to the sum(Budget)-sum(Forecast) output, which in this case will be 100.

Re: Help with CONCAT and Valuelist in a Waterfall Chart

darrellbutler — Tue, 18 Aug 2015 07:55:34 GMT

Graeme,

Thanks for taking the time to reply, not sure if I quite follow what you're saying.

eBrandim should change depending on what level we are at in the hierarchy, but irrespective of the level it will always have 'Budget and 'Forecast' as part of the variable result. When I use eBrandDim in a synthetic valuelist dimension and then reference this in the expression the budget and forecast figures calculate correctly. It's the differences across the brand or region which aren't calculating correctly i.e. they're calculating globally and ignoring dimensions.

Re: Help with CONCAT and Valuelist in a Waterfall Chart

sasiparupudi1 — Tue, 18 Aug 2015 08:15:52 GMT

what do you mean by

It's the differences across the brand or region which aren't calculating correctly i.e. they're calculating globally and ignoring dimensions.

could you please give an example value from your chart selections?

as far as I can see the difference amount seems to be getting calculated correctly

Sasi

Re: Help with CONCAT and Valuelist in a Waterfall Chart

darrellbutler — Tue, 18 Aug 2015 09:20:32 GMT

Hi Sasi,

I've posted a newer app that shows the differences across Brand, this just uses the drill down dimension.

Next to this we have the original table that includes the synthetic dimension.

Instead of showing the individual differences by brand the original table shows the total difference for all brands -260, showing against each brand.

It's got to be something to do with the variable declaration of =eBrand, but if I remove the '=' from this I then lose the brand and region descriptions and just end up with the field name 'Brand' or 'Region' as a dimension.

Hope that makes sense !

Re: Help with CONCAT and Valuelist in a Waterfall Chart

Kushal_Chawda — Tue, 18 Aug 2015 10:08:35 GMT

It's giving you -260 as all because it is synthetic dimension which is not connected with your data.

You have to individually create the expression as

Dimension:

valuelist('Budget','Bianchi','Giant','Trek','Forecast')

Expression:

=pick(match(valuelist('Budget','Bianchi','Giant','Trek','Forecast'),'Budget','Bianchi','Giant','Trek','Forecast'),

sum(Budget),

sum({<Brand={'Bianchi'}>}Forecast)-sum({<Brand={'Bianchi'}>}Budget),

sum({<Brand={'Giant'}>}Forecast)-sum({<Brand={'Giant'}>}Budget),

sum({<Brand={'Trek'}>}Forecast)-sum({<Brand={'Trek'}>}Budget),

sum(Forecast))

See the attached

Re: Help with CONCAT and Valuelist in a Waterfall Chart

darrellbutler — Tue, 18 Aug 2015 15:10:55 GMT

Thanks Kush, that was helpful.

Any ideas how I could get the Budget and Forecast columns plus the differences into a waterfall without loads of hardcoding !