topic Re: Calculated Background of a expression in a pivot based on the average of the dimension in QlikView

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

dominikkeller — Sat, 16 Nov 2024 18:12:46 GMT

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?

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

rushikale0106 — Wed, 19 Aug 2020 11:31:01 GMT

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

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

dominikkeller — Wed, 19 Aug 2020 12:06:45 GMT

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?

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

rushikale0106 — Wed, 19 Aug 2020 12:52:40 GMT

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-expression.htm

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

Thanks,

Rushi

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

Kushal_Chawda — Wed, 19 Aug 2020 16:54:57 GMT

which total you are referring? Can you give example?

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

dominikkeller — Thu, 20 Aug 2020 07:43:17 GMT

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

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

dominikkeller — Thu, 20 Aug 2020 08:22:37 GMT

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

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

Kushal_Chawda — Thu, 20 Aug 2020 09:45:14 GMT

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

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

dominikkeller — Thu, 20 Aug 2020 13:17:25 GMT

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