Calculated Background of a expression in a pivot based on the average of the dimension

[dominikkeller]

Hello everybody,

I have a pivot in which there are three expressions. One needs a calculated background based on the average of the "Filiale" dimension. IDK, I can't find a solution.

This is my expression:

((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto)

Any Ideas?

 

[rushikale0106]

Hi @dominikkeller 

Can you please elaborate on the question? Do you need a calculated background color if the condition matches?

If yes, then you can go to the following block with each expression. It comes with all expressions:

Please see the attached image.

Go to the Background Color and write down your condition, possibly with  'if... ' conditional statement.

Hope it solves your query. Let me know if more explanation is needed. And mark the answer as a solution, it might help others with problems.

Regards,

Rushi

[dominikkeller]

Hey Rushi,

you're right. Im trying to calculaate the background color based on a condition. My Problem is, i want every value that is greater than the average of all values in the dimension 'Filiale'.

In the Image i marked (just as an example) two values. These two values need a red backround because they are higher than the value in total.

So i've tried an if statement, but i'm not able to calculate the avg for the whole dimension.

Got me?

[rushikale0106]

Hi @dominikkeller ,

I guess I understand the issue, try using the following expression in background-color option.

if(avg(Value) > avg(Total aggr(avg(Value), Name)), Blue(), Brown())

for more details, https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Colors/color-by-expressi...

I feel it might solve your problem. Let me know how does it go.

Thanks,

Rushi

[Kushal_Chawda]

which total you are referring? Can you give example?

[dominikkeller]

Hey, the idea is exactly what i need..but i don't find the solution

[dominikkeller]

Hey,

i need the total value of %Anteil zum Netto for each ModifierUpdate. I made a new Image to show it better (and because i had changed the arrangement).

So this is my expression.

((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto)

And my dimensions:

ModifierUpdate (a grouping of Modifier)

Modifier

Filiale.Filialname

In the Dimension of Filiale it works perfect. So now i tried to get the value from the totals. So i can make an if statement. But i don't find the right expression to calculate the total of each ModiferUpdate (the total of all Filiale.Filialname) .

Here are my wrong versions:

//WRONG // if(avg((((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto))) > avg(Total aggr(avg((((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto))), ModifierUpdate)), Blue(), Brown())

//WRONG // if((((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto))>aggr((((Sum(total {$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL Netto)),ModifierUpdate),red(250))

 

[Kushal_Chawda · Accepted solution]

try below. Where highlighted field is your first dimension

=if(SecondaryDimensionality(),
if(((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto)>
avg(total <ModifierUpdate>aggr(((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto),Filiale.Filialname,ModifierUpdate)),red(),
if(((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto)<
avg(total <ModifierUpdate>aggr(((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto),Filiale.Filialname,ModifierUpdate))
Green(),yellow())))

[dominikkeller]

Thank You!

I had to adjust it a little bit, but now it works!

=if(Dimensionality()=2,
if(((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto)>
((avg(total <ModifierUpdate>aggr(((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto),Filiale.Filialname,ModifierUpdate)))/100)*110,red(250),
if(((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto)<
((avg(total <ModifierUpdate>aggr(((Sum({$<ModifierAuswertung={'Modifier'}>}SollVK))/1.16)/sum(TOTAL <Filiale.Filialname>Netto),Filiale.Filialname,ModifierUpdate)))/100)*90,
Green(250),yellow(250))))