topic Re: How do I show pivot results above a certain value ? in QlikView

How do I show pivot results above a certain value ?

Mon, 17 Nov 2014 10:16:51 GMT

Hi Everyone.

I have a pivot table object on my dashboard which shows the sum of budget amount per customer. I want to restrict the list to those customers whose total budgets are greater than £20,000. How do I do this in set analysis ?

Thanks for your help

Re: How do I show pivot results above a certain value ?

ali_hijazi — Mon, 17 Nov 2014 10:19:35 GMT

if your expression is sum(Budget)

then it will be something like this

sum({<Customer={"=sum(Budget)>20000"}>}Budget)

Re: How do I show pivot results above a certain value ?

anbu1984 — Mon, 17 Nov 2014 10:21:32 GMT

If(Aggr(Sum(BudgetAmt),Customer)> 20000,Sum(BudgetAmt))

Re: How do I show pivot results above a certain value ?

Mon, 17 Nov 2014 11:15:23 GMT

Hi Anbu,

What's the advantage of using AGGR in the expression, rather than not using it ?

Re: How do I show pivot results above a certain value ?

anbu1984 — Mon, 17 Nov 2014 11:20:28 GMT

From QV help,

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

Using Aggr() you can do aggregation for the dimensions mentioned in Aggr(), instead of all the dimensions included in your chart.

Re: How do I show pivot results above a certain value ?

simenkg — Mon, 17 Nov 2014 11:38:53 GMT

sum({<Customer={"=sum(BudgetAmount)>20000"}>}BudgetAmount)

Re: How do I show pivot results above a certain value ?

sujeetsingh — Mon, 17 Nov 2014 11:40:17 GMT

Use calculated Diemsion

Re: How do I show pivot results above a certain value ?

Mon, 17 Nov 2014 12:10:26 GMT

Hi Ali,

How do I include a second condition into your expression ?

Type = "Budget"

Re: How do I show pivot results above a certain value ?

anbu1984 — Mon, 17 Nov 2014 12:19:28 GMT

sum({<Customer={"=sum(Budget)>20000"},Type={'Budget'}>}Budget)

Re: How do I show pivot results above a certain value ?

ali_hijazi — Mon, 17 Nov 2014 13:07:41 GMT

Maureen Vickers anbu cheliyan already answered you