Set analysis using a boundary for an integer dimension

[#1]

Hi all,

I need your help for a set analysis expression.

I am using Qlik Sense Server.

Here is my table :

Each row is for a client license.

The max Date is the date that the license expires.

The Risk mainly depend how many days are left before license expiring.

I try to make a "KPI" that displays the sum of the column Risk only for the license that are not yet expired.

Here is my last try :  Sum({<$(vMaxDate) = {">=42286"}>} [$(vRisk)])

But the KPI displays NULL, I don't understand why ?

Thank you for your answers !

Gabriel.

[sunny_talwar]

Hi Gabriel, not sure if this will work as I don't really understand what u r trying to calculate, but try this:

=Sum({<maxDate = {">=42286"}>} Risk)

[#3]

Hi Sunny,

Thank you very much for your answer !

MaxDate and Risk are just the "name" of the columns in the table that display the variables vmaxDate and vRisk. I tested these two variables and they work well.

For example, in the screenshot i sent, all of the maxDates are future dates and so, not expired, so i would like the sum to display 11 (111111113).

Let's assume that the last maxDate is in the past. This would mean that this row (this license) is expired. And then the sum should display 8.

[swuehl · Accepted solution]

It's necessary to know how your variables vMaxDate and vRisk are defined.

vMaxDate needs to expand to a field name, not to an expression. Set Analysis only allows field names on left side of equal sign in a set modifier.

vRisk could expand to an expression, e.g. involving multiple fields, but no further aggregation allowed (e.g. no sum(), count() etc.)

Set Analysis won't be evaluated per dimension line, if you need to do this, use "traditional" aggregation with conditionals, e.g. sum(if(..))

[#5]

Thank you very much swuehl !

I think I understood the problem.

As you can see, my vMaxDate unfortunately expands to two fields name...

Here are the expressions of vRisk and vMaxDate :

vRisk :

if(WildMatch([name-schedule],'*US*')=1 and WildMatch([name-schedule],'*LH*')=1,'1','0')*

  if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 90, 3,

    if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 180, 2, 1) )

+if(WildMatch([name-schedule],'*Local*')=1 ,'1','0')*

  if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 30, 2, 1)

+if(WildMatch([name-schedule],'*EU*')=1 and WildMatch([name-schedule],'*LH*')=1,'1','0')*

  if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 90, 3,

    if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 180, 2, 1) )

vMaxDate :

RangeMax([initial_term_service_end_date], [renewal_term_end_date])

I just managed to make everything work with this traditional formula :

if(WildMatch([name-schedule],'*US*')=1 and WildMatch([name-schedule],'*LH*')=1,'1','0')*

  if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 0, 0,

  if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 90, 3,

    if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 180, 2, 1) ) )

+if(WildMatch([name-schedule],'*Local*')=1 ,'1','0')*

  if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 0, 0,

  if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 30, 2, 1))

+if(WildMatch([name-schedule],'*EU*')=1 and WildMatch([name-schedule],'*LH*')=1,'1','0')*

  if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 0, 0,

  if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 90, 3,

    if ( numsum(RangeMax([initial_term_service_end_date], [renewal_term_end_date])) - numsum(date(now())) < 180, 2, 1) ) )

Just so I know, could you tell me if I could have used the Set analysis in any way ?

Thank you !