Create a Usage Slab

[Anonymous]

Hi Everyone,

I am calculating Usage% metric in my straight table. The calculation is Num(Sum(Nominal)/(Sum(Nominal)+Sum(Undrawn)),'0%').

This usage is being calculated across many dimensions.

Now I want to create a Usage Slab like:

'0%'

'1 – 50%',

'51 – 100%'

and based on a particular slab selection, the data should get filtered.

I tried this in my script using IF, but the problem here is that it is applying the calculation at row level where as my Usage% is an aggregated function calculated in the front end.

I am not sure how to resolve this issue. Any help is highly appreciated.

Thanks

[vinieme12]

please post a sample app

[Kushal_Chawda]

You need to create the calculated dimension in straight table

Aggr(if(Sum(Nominal)/(Sum(Nominal)+Sum(Undrawn)) =0,dual('0%',1),

if(Sum(Nominal)/(Sum(Nominal)+Sum(Undrawn)) >0 and

Sum(Nominal)/(Sum(Nominal)+Sum(Undrawn))<=0.5,dual('1-50%',2),

if(Sum(Nominal)/(Sum(Nominal)+Sum(Undrawn)) >0.5 and

Sum(Nominal)/(Sum(Nominal)+Sum(Undrawn))<=1,dual('51-100%',3)))), dimension1,dimension2, dimension3)

where dimension1,dimension2, dimension3 are your actual straight table dimension, include all dimension of straight table in Aggr

Check Suppress when value is null option

[Anonymous]

I think we cannot do aggr in calculated dimension

[rwunderlich]

On the contrary. In general, you should use Aggr() in Calculated Dimension.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[Kushal_Chawda]

You can use Aggr in calculated dimension

[vinieme12]

infact you should always Aggr() on a calculcated dimension, especially when working with Pivots