Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

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
Highlighted
Creator II
Creator II

stalwar1‌ can you help me with this?

Highlighted

What is your expression for revenue here?

Highlighted

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)

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)
Highlighted
Creator II
Creator II

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)

Highlighted
Creator II
Creator II

I have now attached test data

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

Highlighted
Creator II
Creator II

Thanks a lot,this helped.