topic How to restrict the specific values in an expression from Pivot table in QlikView

How to restrict the specific values in an expression from Pivot table

Tue, 07 Mar 2017 16:47:35 GMT

Hi,

I created a pivot table having an KPI NI Amt, which is having the values of 0,1,2,3,11,14,.........3000.

I want to restrict the values from 0 to 11 in that expression.NI AMt=sum(Ni Amt).Please find the attached qvw.

Thanks,

Krishna

Re: How to restrict the specific values in an expression from Pivot table

vishsaggi — Tue, 07 Mar 2017 16:55:50 GMT

Try this in your expr:

[Ni Amt]

= IF(Sum([NI Amt]) > 11, sum([NI Amt]))

Re: How to restrict the specific values in an expression from Pivot table

sunny_talwar — Tue, 07 Mar 2017 16:56:17 GMT

So only those NI Amt should be added which are between 0 and 11?

Re: How to restrict the specific values in an expression from Pivot table

Anonymous — Tue, 07 Mar 2017 17:31:17 GMT

i think there are two ways to same -

1, at UI level -

if(Aggr(Sum([NI Amt]),<Dimension in >) >11, Sum([NI Amt])

2. In Script - Create a Separate Table for this KPI agianst group by <dimension in chart> sum ([NI Amt]) and then can create flag for < 11 upon it and can use it in UI expression and exclude that Flag.

Hope this will work for you

Re: How to restrict the specific values in an expression from Pivot table

Tue, 07 Mar 2017 17:47:04 GMT

Yes Sunny.

Ex:The Nt Amt Values for ID=28169 are 0 and 11.

I dont want to see the value 11 and also the corresponding ID 28169 in the chart.

Re: How to restrict the specific values in an expression from Pivot table

vishsaggi — Tue, 07 Mar 2017 17:57:17 GMT

Did you try my above expr?

Re: How to restrict the specific values in an expression from Pivot table

Tue, 07 Mar 2017 17:59:59 GMT

This is working but, i am able to see Null values in the expression and corresponding dimension values are appearing in the chart.

Before the logic applies:

After applying the logic.

Re: How to restrict the specific values in an expression from Pivot table

Anonymous — Tue, 07 Mar 2017 18:01:02 GMT

you can also referer to the expression itself... leads to an expression error but works

if([NI Amt]>11,[NI Amt])

Re: How to restrict the specific values in an expression from Pivot table

Tue, 07 Mar 2017 18:02:51 GMT

Logic 1 is working, but still i can see null values and corresponding dimension are appearing in the chart.

Re: How to restrict the specific values in an expression from Pivot table

vishsaggi — Tue, 07 Mar 2017 18:58:10 GMT

Is your Field Total Penalty a dimension or an expression ?If it is an expression try to drag and move the expression field Net Incurred Amount before your Total Penalty field?