topic Re: Sum: exclude dimension null values in QlikView

Sum: exclude dimension null values

edemerdjieva — Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

I have a chart that displays total of sales by business unit for a marketing campaign. In the data set there are sales which are not concerned by the marketing campaing. So I need to exclude these sales from the KPI calculation. This is the equivalent of "Delete null value" option of a dimension. However, I don't have the dimension in question in the chart.

Is there a way to do this in Set Analysis?

Here is the expression of the total sales amount (CA Net TTC):

=Sum(Aggr(Sum(TOTAL <Ticket> [CA Net TTC Devise Support]),Ticket,[Code Action marketing]))

Thanks in advance for your advice!

Re: Sum: exclude dimension null values

YoussefBelloum — Mon, 26 Feb 2018 11:06:07 GMT

Hi,

just add a set analysis on the last Sum to exclude the values you want, like this:

=Sum( {<Field-={'values to exclude'}>} Aggr(Sum(TOTAL <Ticket> [CA Net TTC Devise Support]),Ticket,[Code Action marketing]))

Re: Sum: exclude dimension null values

edemerdjieva — Mon, 26 Feb 2018 11:12:38 GMT

Thanks Youssef!

It worked fine:

{<[Code Action marketing]-=''>}

Re: Sum: exclude dimension null values

YoussefBelloum — Mon, 26 Feb 2018 11:13:34 GMT

You're welcome

Don't forget to close the thread by marking correct and helpul answers.

Good luck

Re: Sum: exclude dimension null values

edemerdjieva — Mon, 26 Feb 2018 11:23:25 GMT

Not sure how to do this.

I marked "Correct answer". Is it closed now?

Re: Sum: exclude dimension null values

YoussefBelloum — Mon, 26 Feb 2018 12:33:31 GMT

Yes, it is in green