topic Re: Restrict calculation based on Null date in App Development

Restrict calculation based on Null date

tsachdev421 — Fri, 15 Apr 2022 19:32:56 GMT

How to restrict output based on null date value??

Re: Restrict calculation based on Null date

vchuprina — Sat, 16 Apr 2022 07:14:33 GMT

Hi,

In Qlikview you can suppress Null values by selecting check box 'Suppress When Value is Null' on the dimension tab

In QlikSense it works in the same way - deselect 'Include null values' checkbox in your dimension

Regards,

Vitalii

Re: Restrict calculation based on Null date

tsachdev421 — Sat, 16 Apr 2022 07:38:26 GMT

That works on dimensions not measures. Please refer the image i have attached of the problem.

Re: Restrict calculation based on Null date

Vegar — Sat, 16 Apr 2022 07:42:50 GMT

The solution of @vchuprina will work as long you have the [Date 2] as a dimension in your object. If you don't want the dimension in your object then you can solve this by using SET analysis.

Try something like this: SUM({<[Date 2]*={"*"}>}Sales)

Re: Restrict calculation based on Null date

tsachdev421 — Sat, 16 Apr 2022 07:50:27 GMT

Thanks for taking out time to look into this. Not using Date2 as dimension in my chart. Plus is there anyway? I can use existing variable $(=$(vSales(utilisation)) and restrict data of null dates.

I have tried creating a flag for null dates. But using this variable within set analysis is giving me some error.

Re: Restrict calculation based on Null date

Vegar — Sat, 16 Apr 2022 08:13:33 GMT

I assume that your dollar expanded variable will result inbsome kind of aggregating function, let's call it Sum({$}X)

There is no way to insert additional modifiers into that expression without modifying the variable it self. You could however try to wrap en additional aggregation function, eg. Sum(), around it there get your desired result, but to do do that you need to create an array of values from your variable expression by using the aggr() function.

It would look something like this

SUM({${...}}AGGR(SUM({$}X),[Dim1],[Dim2],[DimN]))

Where Dim1-N is the dimensions used for your inner aggregation. I guess that you might be helped by something like this:

SUM({<[Date 2]*={"*"}>}AGGR($(=$(vSales(utilisation)),[Date 1],[Date 2]))

Re: Restrict calculation based on Null date

tsachdev421 — Sat, 16 Apr 2022 09:18:32 GMT

I guess, I'm close to the expected result..

In the backend I have setup a Flag

Isnull(Date2) as DateFlag..

On the frontend i used an expression

SUM({<DateFlag={'0'}>}AGGR($(=$(vSales(utilisation)),[Date 2],[Region]))

I could see the total 1400 but when apply the same expression in table chart output is 2800 (Country+region)

Country= region 1+ region 2+ region 3

Re: Restrict calculation based on Null date

vchuprina — Sat, 16 Apr 2022 09:24:35 GMT

Could you please share your data model and expression in variable$(vSales(utilisation))?

If you have Date2 and Sales field in one table, you can just create a new sales column and update your expression

if(isnull(Date2), null(), Sales) as Sales2

Regards,

Vitalii