ValueList

[#1]

Hi everybody

I built a value list containing 5 text values (TO, MS, Growth, ...) to use in a pivot table.

On a selection I get a TO total = 1 000 . This is the right result I get when using a pivot table and an expression sum(TO).

When I add ValueList as a dimension and change my expression with If ($(ValueList)='TO', sum(TO)) the result is 5 000. Means I have 5 times the result because I've 5 values in my list.

Does someone has a clue to solve it?

best regards

christian

[#2]

Hello Christian,

corrrect expression is

sum( If ($(ValueList)='TO', TO, 0))

This means you sum up within your sum() only values passing the filter of the if(). In your expression there will be a total sum (=5000) when if() is true. This is at least once and 5000 being your sum of all values is returned.

HtH

Roland

[#3]

Hi Roland,

thank you for your answer.
It works better but only for 2 levels.
If I have to tell everything we use a pivot table with 4 geographic levels as dimensions + 1 ValueList dimension.
As expression we have

if ($(ValueList) = 'TO', sum(TO),
if ($(ValueList) = 'MS', sum(TO) / sum( {<Product>} total <Geo1, Geo2, Geo3> TO,
.... for the 5 values

So we switch for your solution only for 1 value in the list,
sum (if ($(ValueList) = 'TO', TO),
and it gives a good result but only for last level and Geo3 level. Geo1 and Geo2 levels are wrong but not 5 times anymore just a bit more than twice the expected result

i'm not sure about something like this because we have to test the value and to divide one expression by another

sum (
if ($(ValueList) = 'TO', TO,

if ($(ValueList) = 'MS', TO) / {<Product>} total <Geo1, Geo2, Geo3> TO,
...)))))

best regards
christian

[#4]

Hi Christian,

to clearify some details: are you able to send a little example app? Perhaps with some sample data and the results you expect for one or two given cases. I am sure this will shorten our correspondence and helps me to help you.

Regards, Roland

[#5]

Hi Roland

thank you for your help. I join an example. I hope it will be clear for you

best regards

christian

[#6]

Hi Christian,

had the time to take a glance at your app. I think I found what you want to achieve. Regardez in your first sum() of your nested if() "Tot Market":

if($(ValueListIndic)='Pdm',
 sum({<Prod_CommonAppellation,Prod_CompositionGroup>} total <Secto_Area, Secto_DistrictLib,Secto_TerritoryLib> If(Vte_AMJ01=Date_AMJ01 and $(ValueListIndic)='Pdm' , Vte_UNC)),

Note: The if() within the sum() is the filter of your sum() and I only added the part above in italic. With other words, the outer nested if and the filter if() within the sum-function are independent.

RR