Restrict calculation based on Null date

[tsachdev421]

How to restrict output based on null date value??

[vchuprina]

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

[tsachdev421]

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

[Vegar]

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)

[tsachdev421]

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.

[Vegar]

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

[tsachdev421]

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

[vchuprina]

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