# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for
Did you mean:
Contributor III

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

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?

Labels (2)

• ### Pivot Table Expression

1 Solution

Accepted Solutions

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())))

8 Replies
Contributor III

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:

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

Contributor III
Author

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?

Contributor III

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())

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

Thanks,

Rushi

which total you are referring? Can you give example?

Contributor III
Author

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

Contributor III
Author

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

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())))

Contributor III
Author

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

Community Browser