topic Re: Create a Usage Slab in QlikView

Create a Usage Slab

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Create a Usage Slab

vinieme12 — Wed, 29 Mar 2017 12:15:09 GMT

please post a sample app

Re: Create a Usage Slab

Kushal_Chawda — Wed, 29 Mar 2017 12:41:26 GMT

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

Re: Create a Usage Slab

Anonymous — Wed, 29 Mar 2017 14:10:41 GMT

I think we cannot do aggr in calculated dimension

Re: Create a Usage Slab

rwunderlich — Wed, 29 Mar 2017 15:54:40 GMT

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Create a Usage Slab

Kushal_Chawda — Wed, 29 Mar 2017 16:56:45 GMT

You can use Aggr in calculated dimension

Re: Create a Usage Slab

vinieme12 — Thu, 30 Mar 2017 05:55:30 GMT

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