Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prasadcm
Creator II
Creator II

If condition in set analysis

Hello All,

So the data is like below

 

Time Key RelativeENTTY_IDENTTY_NMNew_ProductTotalProd1Prod2Prod3Prod4Prod5Prod6Prod7Prod8Prod9Prod10Prod11test
AUG 2018Total10,770.191,023.04568.58165.80236.06947.201,738.63673.491,289.48427.61225.933,474.36          3+
AUG 2018123Entity16.342.02-----0.940.32-0.013.051
AUG 2018456Entity22.650.70--0.110.330.000.380.13-0.001.001
AUG 2018789Entity30.690.280.00----0.050.01--0.341
AUG 20181010Entity42.79-0.01-0.14---0.200.020.010.012.421

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

1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

7 Replies
prasadcm
Creator II
Creator II
Author

stalwar1‌ can you help me with this?

sunny_talwar

What is your expression for revenue here?

Anil_Babu_Samineni

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
prasadcm
Creator II
Creator II
Author

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)

prasadcm
Creator II
Creator II
Author

I have now attached test data

sunny_talwar

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))

prasadcm
Creator II
Creator II
Author

Thanks a lot,this helped.