topic Re: Set analysis in QlikView

Set analysis

polisetti — Tue, 05 Aug 2014 19:59:00 GMT

Hi,

I have a pivot table as shown below. I need the sales in the sales column, but in the total row, i need only the sum of products having flag=x. Can anyone suggest some expression for the same.

Product	Flag	Sales
A	x	10
B	x	20
C	y	40
Total		30

If we write an expression for sales as sum(sales) where flag is minimum, the total row will consider only flag=x and I will be able to get the sales across each product as well.

Any suggestions for the expression?

Regards,

Pollisetti

Re: Set analysis

joshabbott — Tue, 05 Aug 2014 20:01:27 GMT

=sum(<flag={'x'}>sales)

The value wouldn't show for the 'y' flag, but the others should show 10 and 20 respectively with a total of 30 for the 2nd expression column

Re: Set analysis

jerem1234 — Tue, 05 Aug 2014 20:01:36 GMT

Try something like:

if(dimensionality()=0, sum({<Flag = {'x'}>}Sales), sum(Sales))

Hope this helps!

Re: Set analysis

polisetti — Tue, 05 Aug 2014 20:02:41 GMT

I want the value to be shown for Y as well but not to be considered in total

Re: Set analysis

polisetti — Tue, 05 Aug 2014 20:04:35 GMT

can u suggest me the expression to calculate:

sum(flag=min(flag) sales)

Re: Set analysis

jerem1234 — Tue, 05 Aug 2014 20:06:57 GMT

Something like:

sum({<Flag={$(=min(Flag))}>}Sales)

Re: Set analysis

Tue, 05 Aug 2014 20:24:34 GMT

I can think of a lengthy workaround.

1. Create expression containing the basic sales - Expr1

= sales // Total here becomes null

2. Create second expression - Expr2

= sum(if(min(all flag) = flag,sales)) // Total here is sum of sales where flag is min

3. Create third expression which is based on first two

= if( Expr2 = 0, Expr1, Expr2) // This will give the column that you need

4. Hide the first two expressions.(option available in Presentation tab of chart properties)