Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

prasadcm
Contributor 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
MVP
MVP

Re: If condition in set analysis

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

7 Replies
prasadcm
Contributor II

Re: If condition in set analysis

stalwar1‌ can you help me with this?

MVP
MVP

Re: If condition in set analysis

What is your expression for revenue here?

Re: If condition in set analysis

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)

prasadcm
Contributor II

Re: If condition in set analysis

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
Contributor II

Re: If condition in set analysis

I have now attached test data

MVP
MVP

Re: If condition in set analysis

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
Contributor II

Re: If condition in set analysis

Thanks a lot,this helped.