If Not Match Question

[KPage]

Hi,

I am trying to omit "Remaining Retail" and "Total Retail". I have the expression below, but my data is showing a dash mark (very bottom row), with the total sales. I want to remove that. How do I do that?

=if(not match(Outlet, 'Total Retail', 'Remaining Retail'), Outlet)

[Digvijay_Singh]

Check if this property helps - 

[Oleg_Troyansky · Accepted solution]

Well, the reason for the NULL value is the essence of the formula - it uses the fields with no aggregation, hence at the total row all possible values of the field Outlet are possible, while the formula is expecting a single value.

Also, solving this issue in a calculated Dimension is quite wasteful - it adds unnecessary overhead, which could be substantial in large data sets.

So, a better solution would be to resolve it in the Measure, using Set Analysis, for example like this:

sum({<Outlet -= { 'Total Retail', 'Remaining Retail'}>}  Sales) 

With this measure, the Dimension can be simply "Outlet", with no IF conditions.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin! I'm teaching solutions like this one in my session on Set Analysis and AGGR.

[KPage]

That worked! Thank you so much!