if condition set analysis

[Anonymous]

Hi all,

I am trying to do something like

if(var=0, count({<[V.Month]={'$(=Max([V.Month]))'}>} distinct ItemID),

     if(var=1, count({$<"Found in X field"={'Yes'}>*<[V.Month]={'$(=Max([V.Month]))'}>} distinct ItemID),

          if(var=2, count({$<"Found in Y field"={'Yes'}>*<[V.Month]={'$(=Max([V.Month]))'}>} distinct ItemID))))

for a bar chart which shows the latest month data. I only want to include the 'Yes' values in the "Found in X field" for this bar chart, hence the if statement.

Without the if condition the below expression works

count({$<"Found in X field"={'Yes'}>*<[V.Month]={'$(=Max([V.Month]))'}>} distinct ItemID)

Any suggestions on how to approach this? Thank you

[YoussefBelloum]

Hi,

can you add sample data ? var is a variable ?

[Anonymous]

Hi,

Am afraid it would be too tedious to upload a sample file as I have confidential data and a few mapping tables

var is a calculated field of either 0,1 or 2. The Found in X/Y fields are calculated fields as well and are either 'Yes' or 'No'.

[swuehl]

If you "only want to include the 'Yes' values in the "Found in X field" for this bar chart, hence the if statement." and the single aggregation

count({$<"Found in X field"={'Yes'}>*<[V.Month]={'$(=Max([V.Month]))'}>} distinct ItemID)

returns the correct result ("it works"), what's your exact issue? Why do you need the if() Statement at all?

[Anonymous]

Oops my bad, I meant I only want to include the 'Yes' values in the "Found in X field" as well as for the "Found in Y field" for this bar chart.

I need the if statement as the set analysis depends on the var field.

Eg. var=0 => No need to filter out "Found in __ field"

var=1 => Need to filter out "No" values from "Found in X field"

var=2 => Need to filter out "No" values from "Found in Y field"

[swuehl]

If you are saying, that var is a calculated field, you mean that you need to consider this field (check against its value) on a per record base?

As Youssef already mentioned, some more details, like some sample data (we don't need you full and real data, but anything that explains your Setting), would really help us to help you.

[Anonymous]

Hi Stefan,

Yes I believe that is what I am trying to achieve.

The bar chart's Dimensions are "Business Domain" and Rating (High/Medium/Low). I am trying to get the Measure to calculate the total values for only 'Yes' values unless var=0.

var, Found in X and Y are derived as follows:

if(SubField(Round(Num(Month($(vMonth)))/3, 0.01),'.',2)=33, 0,

  if(SubField(Round(Num(Month($(vMonth)))/3, 0.01),'.',2)=67, 1,

        2)) as varMonth // $(vMonth) is taken from the file name

if(varMonth=0 or "First Discovered"=Null(), Null(), /if 1st month or First Discovered field is null then null

  if(isNull(ApplyMap('mapTrue1stDate', VulnIDMth, Null())), 'No', //if 2nd/3rd month and ApplyMap is null => no

        'Yes')) as "Found in X",

  if(varMonth=0 or varMonth=1 or "First Discovered"=Null(), Null(), //if 1st or 2nd month then null

  if(isNull(ApplyMap('mapTrue1stDate', VulnIDMth, Null())), 'No',  //if 3rd month and ApplyMap is null => no

        'Yes')) as "Found in Y",

Hope the mentioned details are relevant. Thanks!

[Anonymous · Accepted solution]

Found this document helpful and managed to (understand set analysis better) display the correct numbers!

https://community.qlik.com/docs/DOC-4951

Used the expression below

Count({$<[V.Month]={'$(=Max([V.Month]))'}, varMonth={"0"} >+<[V.Month]={'$(=Max([V.Month]))'}, "Found in 2nd mth?"={'Yes'}, varMonth={"1"}>+<[V.Month]={'$(=Max([V.Month]))'}, "Found in 3rd mth?"={'Yes'}, varMonth={"2"} >} distinct [%ItemID])

Thanks all for the help!

[smithaprabhakar]

Hi,

Can anyone help me out for the below expression to write using set analysis.

if(AbsoluteThrshold,-1,fabs(sum({<$(='[$(premiumField)]')))>=vAbsolutethreshold)

FYI:

AbsoluteThrshold--> This is the dynamic calculated dimension inside the table wherein after meeting the thrshold value the row wise data is displayed.  and AbsoluteThrshold value is written inside the same table