topic Re: Calculated Dimension in QlikView

Calculated Dimension

upaliwije — Thu, 19 Sep 2019 09:11:05 GMT

Hi Friends,

I have created a calculated Dimension with following command in my pivot table

=If(Aggr(Rank(Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/sUM({<VEHICLE_TYPE={'CAR/JEEP'}>}POLNos))>5, [MAKE])<=10, [MAKE], 'Others')

But It does no work properly.

My requirement is to Show 10 records of Vehicle Make where the value of the following condition is more than 5

Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/sUM({<VEHICLE_TYPE={'CAR/JEEP'}>}POLNos))>5

Kindly help me to get the correct syntax please.

Re: Calculated Dimension

sunny_talwar — Thu, 19 Sep 2019 11:43:44 GMT

May be this

=Aggr( If( Rank( Sum({<MAKE = {"=Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/Sum({<VEHICLE_TYPE = {'CAR/JEEP'}>} POLNos) > 5"}, VEHICLE_TYPE = {'CAR/JEEP'}>} CLMPCNT) / Sum({<MAKE = {"=Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/Sum({<VEHICLE_TYPE = {'CAR/JEEP'}>} POLNos) > 5"}, VEHICLE_TYPE = {'CAR/JEEP'}>}POLNos) ) <= 10 , [MAKE], 'Others') , [MAKE])

Re: Calculated Dimension

upaliwije — Fri, 20 Sep 2019 09:59:33 GMT

Thanks Sunny

I applied your syntax but it does not give required out put

What I want i some thing similar to below output

Pls help

Re: Calculated Dimension

upaliwije — Fri, 20 Sep 2019 10:02:37 GMT

Re: Calculated Dimension

sunny_talwar — Fri, 20 Sep 2019 11:42:19 GMT

Would you be able to share a sample so that we can see the issue?

Re: Calculated Dimension

upaliwije — Fri, 20 Sep 2019 16:20:17 GMT

Dear Sunny,

This is my sample Appreciate your help

Re: Calculated Dimension

sunny_talwar — Fri, 20 Sep 2019 16:35:08 GMT

It seems that your expression =Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/Sum({<VEHICLE_TYPE = {'CAR/JEEP'}>} POLNos) never gets above 1... look at the image below

Now this might be just for the sample file you have shared. So, in order to make the condition meet, I changed the condition from >5 to > 0.5... and I see this

Re: Calculated Dimension

upaliwije — Fri, 20 Sep 2019 16:52:42 GMT

Thanks sunny ,

But in my full data set scenario is as shown below

Pls suggest solution for it. I also want this to be sorted in descending order with 'Others' shown as last item please

Re: Calculated Dimension

sunny_talwar — Fri, 20 Sep 2019 17:24:35 GMT

Based on the data provided in the sample app... what is the expected output?

Re: Calculated Dimension

upaliwije — Sat, 21 Sep 2019 00:36:18 GMT

Expected out put is there for the sample data but it has to be sorted descending order. How ever when It applies my full data set it wont work.

Re: Calculated Dimension

Brett_Bleess — Tue, 01 Oct 2019 16:23:35 GMT

Just kicking this back up in the list with a comment to see if Sunny or someone else has any other ideas on things.

Regards,
Brett