Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dominikkeller
Contributor III
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?

qlik.png

Labels (2)
1 Solution

Accepted Solutions
Kushal_Chawda

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

View solution in original post

8 Replies
rushikale0106
Contributor III
Contributor III

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
Contributor III
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?

rushikale0106
Contributor III
Contributor III

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
Contributor III
Contributor III
Author

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

dominikkeller
Contributor III
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))

Bild-1.jpg

Kushal_Chawda

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

dominikkeller_0-1597929372671.png