topic Need help in correcting Utilization % formula in QlikView

Need help in correcting Utilization % formula

farheenayesha — Tue, 20 Mar 2018 15:11:25 GMT

Hi,

I am trying to calculate Utilization % using below formula. With the below formula ill get correct values if I select a brand. If i dont select any brand then the denominator is getting added up for both the brands.

Formula: Count(distinct Gov)/count(distinct total Rst)

Ex: If i select Brand 1 then i get the correct result as below

Brand	Sheet	Gov	RST	Utilization
Brand1	Sheet1	131	133	98.5%
	Sheet2	131	133	98.5%
	Sheet3	128	133	96.2%
	Sheet4	125	133	94.0%
	Sheet5	122	133	91.7%
	Sheet6	104	133	78.2%
	Sheet7	130	133	97.7%
	Blank	99	133	74.4%
	Sheet8	75	133	56.4%
	Sheet9	80	133	60.2%
	Sheet12	41	133	30.8%
	Sheet11	17	133	12.8%

However if I select both the brands (Brand1 and Brand2) then i get the below result which is incorrect. In the below result denominator is getting added up. Hence getting Rst as 183(133+50).

But in the result i want Rst for Brand1 as 133 and for Brand 2 as 50. Kindly help.

Brand	Sheet	Gov	Rst	Utilization_1
Brand1	Sheet1	131	183	71.6%
	Sheet2	131	183	71.6%
	Sheet3	128	183	69.9%
	Sheet4	125	183	68.3%
	Sheet5	122	183	66.7%
	Sheet6	104	183	56.8%
	Sheet7	130	183	71.0%
	Blank	99	183	54.1%
	Sheet8	75	183	41.0%
	Sheet9	80	183	43.7%
	Sheet12	41	183	22.4%
	Sheet11	17	183	9.3%
Brand2	Sheet1	50	183	27.3%
	Sheet2	50	183	27.3%
	Sheet2	49	183	26.8%
	Sheet4	49	183	26.8%
	Sheet5	49	183	26.8%
	Sheet6	48	183	26.2%
	Sheet7	25	183	13.7%
	Sheet8	44	183	24.0%
	Sheet9	38	183	20.8%
	Blank	48	183	26.2%
	Sheet10	42	183	23.0%
	Sheet11	6	183	3.3%

I want the final result as below

Brand	Sheet	Gov	RST	Utilization
Brand1	Sheet1	131	133	98.5%
	Sheet2	131	133	98.5%
	Sheet3	128	133	96.2%
	Sheet4	125	133	94.0%
	Sheet5	122	133	91.7%
	Sheet6	104	133	78.2%
	Sheet7	130	133	97.7%
	Blank	99	133	74.4%
	Sheet8	75	133	56.4%
	Sheet9	80	133	60.2%
	Sheet12	41	133	30.8%
	Sheet11	17	133	12.8%
Brand2	Sheet1	50	50	100.0%
	Sheet2	50	50	100.0%
	Sheet2	49	50	98.0%
	Sheet4	49	50	98.0%
	Sheet5	49	50	98.0%
	Sheet6	48	50	96.0%
	Sheet7	25	50	50.0%
	Sheet8	44	50	88.0%
	Sheet9	38	50	76.0%
	Blank	48	50	96.0%
	Sheet10	42	50	84.0%
	Sheet11	6	50	12.0%

stalwar1‌theresaa‌

Re: Need help in correcting Utilization % formula

sunny_talwar — Tue, 20 Mar 2018 15:13:15 GMT

Maybe this

Count(DISTINCT Gov)/Count(DISTINCT TOTAL <Brand> Rst)

Re: Need help in correcting Utilization % formula

qlikview979 — Tue, 20 Mar 2018 16:46:20 GMT

Hi sunny,

Thankyou

As per the above expression value is coming correct but also we need to ignore filter condition in the above expression

Actual expression:

Count(DISTINCT UNIX_ID_GOV)/Count( distinct total {<

Node_TS_Year=,QuarterName=,MonthName=,WeekName=,DateRange=,CalcAuditSheetName=,Sheet_Name=>}UNIX_ID_RST)

when we tried to add brand like below it is showing set modified error

Count(DISTINCT UNIX_ID_GOV)/Count( distinct total {< <Brand>

Node_TS_Year=,QuarterName=,MonthName=,WeekName=,DateRange=,CalcAuditSheetName=,Sheet_Name=>}UNIX_ID_RST)

so how to add below filters in set expression with brand group

Node_TS_Year=,QuarterName=,MonthName=,WeekName=,DateRange=,CalcAuditSheetName=,Sheet_Name=

Regards

Mahesh

Re: Need help in correcting Utilization % formula

sunny_talwar — Tue, 20 Mar 2018 16:49:26 GMT

Adding the total qualifier within set analysis? That is not right... it has to be outside of set analysis's curly brackets

Count(DISTINCT UNIX_ID_GOV)/

Count(DISTINCT TOTAL <Brand> {<Node_TS_Year, QuarterName, MonthName, WeekName, DateRange, CalcAuditSheetName, Sheet_Name>} UNIX_ID_RST)

Re: Need help in correcting Utilization % formula

farheenayesha — Wed, 21 Mar 2018 07:36:26 GMT

Thank you so much Sunny. You are Great