topic If condition in set analysis in QlikView

If condition in set analysis

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

Hello All,

So the data is like below

Time Key Relative	ENTTY_ID	ENTTY_NM	New_Product	Total	Prod1	Prod2	Prod3	Prod4	Prod5	Prod6	Prod7	Prod8	Prod9	Prod10	Prod11	test
AUG 2018	Total			10,770.19	1,023.04	568.58	165.80	236.06	947.20	1,738.63	673.49	1,289.48	427.61	225.93	3,474.36	3+
AUG 2018	123	Entity1		6.34	2.02	-	-	-	-	-	0.94	0.32	-	0.01	3.05	1
AUG 2018	456	Entity2		2.65	0.70	-	-	0.11	0.33	0.00	0.38	0.13	-	0.00	1.00	1
AUG 2018	789	Entity3		0.69	0.28	0.00	-	-	-	-	0.05	0.01	-	-	0.34	1
AUG 2018	1010	Entity4		2.79	-0.01	-	0.14	-	-	-	0.20	0.02	0.01	0.01	2.42	1

If the revenue is more than 0.05 in any 3 product for an entity then entity should be counted under 3+ category.
I need total of all such entities in text box.

How can I do this in script or UI level?

Regards,

Prasad Mayekar

stalwar1‌

Re: If condition in set analysis

prasadcm — Wed, 17 Oct 2018 09:20:03 GMT

stalwar1‌ can you help me with this?

Re: If condition in set analysis

sunny_talwar — Wed, 17 Oct 2018 10:19:32 GMT

What is your expression for revenue here?

Re: If condition in set analysis

Anil_Babu_Samineni — Wed, 17 Oct 2018 10:38:06 GMT

How about this? Not sure exactly what you are referring? Can you share sample output

Sum(TOTAL <New_Product> {<New_Product = {"=Sum(Revenue)>0.05"}>} Revenue)

Re: If condition in set analysis

prasadcm — Wed, 17 Oct 2018 10:44:49 GMT

SUM({$<Measure={'Revenue'},SummaryItem={5},[Time Key Reported] = {'Current Rolling 12 Months'}>}[Rev ex UFR])

expression in table

I need to count entities where revenue is more than 50,000 in any 3 or more product.
So I'm trying to get count in textbox with below expression

=count(DISTINCT{<[ENTTY_ID] = {"=if(count(DISTINCT {<New_Product={"=if(sum({<[HQ_MNC_Flag]={'HQ'},SummaryItem={5},[Time Key Reported]={'Current Rolling 12 Months'}>}[Rev ex UFR])>50000,1,0)"}>}New_Product)>3,1,0)"}>} ENTTY_ID)

Re: If condition in set analysis

prasadcm — Wed, 17 Oct 2018 12:05:10 GMT

I have now attached test data

Re: If condition in set analysis

sunny_talwar — Wed, 17 Oct 2018 13:38:27 GMT

Try this

=Count(DISTINCT Aggr(If(Count(Aggr(If(Sum([Rev UFR]) > 50000, New_Product), [Time Key Relative], [Time Period], ENTTY_ID, New_Product)) >= 3, ENTTY_ID), [Time Key Relative], [Time Period], ENTTY_ID))

Re: If condition in set analysis

prasadcm — Wed, 17 Oct 2018 15:53:05 GMT

Thanks a lot,this helped.