Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Fix Table Values?

Hi all,

I got a specific problem with a table. As you can see in the picture below I got a table with some values on the dimension "Zeitzone". The values are all correct. The problem occurs, when I make a selection of the dimension (

First.png

The problem occurs, when I make a selection of the dimension (see below)

Second.png

The expression in the table for the "Abweichung" is this: =(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum(TOTAL [n/n/n])))*100

The problem is the red marked part. The value should only change, when I select a year, but not when I select any other dimension. Is it possible to do this?

Or is there another way to fix the values in the table that it shows "0,2" for "13:00-17:00" and not "-85,3".

I hope I made my problem clear to you and be glad for any help! If there are any questions, please ask.

Thx

Daniel

1 Solution

Accepted Solutions
Jason_Michaelides
Partner - Master II
Partner - Master II

The TOTAL qualifier ignores the current row (dimension value) but is still based on the available dimension values, so if you select a dimension value (in this case 13:00-17:00) then you are limiting the available dimension values in the chart and TOTAL will adjust accordingly. You need to also exclude the dimension selection.

Instead of TOTAL, try {<Zeitzone=>} TOTAL giving:

=(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum({<Zeitzone=>}TOTAL [n/n/n])))*100

Hope this helps,

Jason

View solution in original post

5 Replies
Not applicable
Author

Hi,

It would have been better had you attached the app. From what i understood you can try set analysis using this --

Avg({<zeitzone=,year={$(=only(year)-1}>}[Netto-Plan-ZZ]).. Ididnt write the full expression as i am not getting the logic .

sivarajs
Specialist II
Specialist II

The problem is you are using total function, whenever you are selecting some dim it will show the same value for all

try this

Avg({$<Zeitzone>}[Netto-Plan-ZZ])-(Sum([n/n/n])/Sum(TOTAL [n/n/n])))*100 ------it will exclude the selection

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

Check something like this

=(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum(TOTAL {1} [n/n/n])))*100

Celambarasan

Jason_Michaelides
Partner - Master II
Partner - Master II

The TOTAL qualifier ignores the current row (dimension value) but is still based on the available dimension values, so if you select a dimension value (in this case 13:00-17:00) then you are limiting the available dimension values in the chart and TOTAL will adjust accordingly. You need to also exclude the dimension selection.

Instead of TOTAL, try {<Zeitzone=>} TOTAL giving:

=(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum({<Zeitzone=>}TOTAL [n/n/n])))*100

Hope this helps,

Jason

Not applicable
Author

That was what I was looking for!

Excluding the dimension. Didn't think of this. Sometimes, it's that easy.

Thanks all of you for your help!