topic Re: Set Analysis on result current selection in QlikView

Set Analysis on result current selection

Tue, 21 Feb 2017 10:12:00 GMT

Good morning,

I'm using a chart where I compare the sales per client per Business Unit with the sales per client total.

In my current selections properties I have chosen a particular Business Unit and it gives me a result. I use Set Analysis in a new expression to exclude the 'Business Unit' selection and provide me with the total sales per client.

I'm using this expression:

sum({<BusinessUnit=>} Sales)

The problem is: I have two columns. The sales per client per Business Unit (column 1) and the total sales per client (column 2). I want to compare only the sales per business unit/client with the total sales per client where there is a result in column 1. With my current expression I have also results in column 2 where there aren't any sales in column 1.

Is there a simple solution for this problem? I'm a beginner with Qlikview so excuse me if the explanation is a little bit unclear.

Regards,

Timo

Re: Set Analysis on result current selection

sunny_talwar — Tue, 21 Feb 2017 11:05:48 GMT

May be just this

If(Column(1) > 0, Sum({<BusinessUnit=>} Sales))

Sum({<BusinessUnit=>} Sales) * Avg(1)

Re: Set Analysis on result current selection

Tue, 21 Feb 2017 12:35:41 GMT

Thank you very much. This was very helpful!

Re: Set Analysis on result current selection

Wed, 22 Feb 2017 12:45:23 GMT

Is there a simple solution to embed this code into the code beneath. With the code beneath I select last year's sales. How do I add an Set Analysis expression to this?

sum({<Jaar=, Maandnaam={'>=$(#=AddMonths(min(Maandnaam),-12)) <=$(#=AddMonths(max(Maandnaam),-12))'}, Maand = ,Dag = >} Sales)

Re: Set Analysis on result current selection

sunny_talwar — Wed, 22 Feb 2017 14:08:44 GMT

May be this:

Sum({<Jaar =, Maandnaam = {'>=$(#=AddMonths(min(Maandnaam),-12))<=$(#=AddMonths(max(Maandnaam),-12))'}, Maand = , Dag = , BusinessUnit = >} Sales)

or this if you need the same logic of making this 0

Sum({<Jaar =, Maandnaam = {'>=$(#=AddMonths(min(Maandnaam),-12))<=$(#=AddMonths(max(Maandnaam),-12))'}, Maand = , Dag = , BusinessUnit = >} Sales) * Avg({<Jaar =, Maandnaam = {'>=$(#=AddMonths(min(Maandnaam),-12))<=$(#=AddMonths(max(Maandnaam),-12))'}, Maand = , Dag = >} 1)