Help with CONCAT and Valuelist in a Waterfall Chart

[darrellbutler]

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

[#2]

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.

[darrellbutler]

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.  

[sasiparupudi1]

Hi

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

[darrellbutler]

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 !

[Kushal_Chawda]

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

[darrellbutler]

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 !