topic Re: How to use data handling - Calculation condition in App Development

How to use data handling - Calculation condition

Luis_Saavedra — Thu, 11 Aug 2022 22:42:57 GMT

I have a pivot table on Qlik sense that have three columns, Current, Prior and Variation.

Current is a measure with a set analysis with many conditions that show amount related to sell done today and

Prior is also a measure with a set analysis with many conditions that show amount related to sell done yesterday.

Variation is just measure Current – measure Prior.

I need to be able to filter the pivot table by Variation to show only rows where variation is greater than zero or is not null, something similar as we can do on Power BI.

thank for your help

Table below

Re: How to use data handling - Calculation condition

Digvijay_Singh — Fri, 12 Aug 2022 01:15:37 GMT

When a chart has multiple measures, whole row cannot be eliminated when one of the measure has value greater than 0. I would check in each measure like

if(Current measure exp - Prior measure expression >0, Current measure exp, 0)

same with prior measure as well. And then use chart property to exclude zero values.

Re: How to use data handling - Calculation condition

Luis_Saavedra — Fri, 12 Aug 2022 16:25:25 GMT

Thanks!

ti seems this is a weak on the way qlik sense works vs Power BI 😞

I tried the way you mentioned bot it does not work for me do it in that way but thanks a lot for making time to reply mi inquiery.

Re: How to use data handling - Calculation condition

marcus_sommer — Mon, 15 Aug 2022 12:53:55 GMT

You may need to extend the suggestion from @Digvijay_Singh for a NULL handling because if any of the operands has not a valid numeric value the whole part becomes NULL. This might be queried with isnull() within if-loops or directly fetched with alt() or coalesce() and/or bypassed with a range-function. Therefore you may try something like this:

if(rangesum(Current measure exp, -Prior measure expression) >0, Current measure exp, 0)

Depending on the scenario you may also replace the zero from the else-branch with an explicit null() which won't be displayed with the defaults.

An alternatively way to the filter within the expression might be to create a calculated dimension with it within a list-box or also as chart-dimension, like:

aggr(if(condition, expr), Dim1, Dim2)

respectively to bypass the condition-stuff by clustering the results, with:

class(aggr(expr, Dim1, Dim2), $(vClusterSize))

For rather fixed reporting-purposes might the last suggestion not very suitable but for the sake of analysing the data it's much more powerful.

- Marcus