topic SET ANALYSIS USING VARIABLE AS FIELD IN CONDITION in QlikView

SET ANALYSIS USING VARIABLE AS FIELD IN CONDITION

maudifer — Sat, 16 Nov 2024 04:00:11 GMT

Hi All,

I wrote the below expression but when using variable as filed in the condition, it does not work.

It seems to be an issue with the syntax of the field (or variable) v(Metric%).

I need it to be a dynamic filed name, as the condition changes with the fields.

Basically i want my expression to divide the count of 'ON TIME' for the selected dynamic field divided by the sum of 'ON TIME' and 'LATE' for the selected dynamic field.

So i need both fields in red and green to be dynamic.

The ones in green seem to work fine, but not the ones in red.

Can you help ?

COUNT(DISTINCT{<[MonthID2]={">=$(vPrevMonth5ID)<=$(vCurrMonthID)"},$(vMetric%) = {'ON TIME'}>}$(vMetric%))/
(COUNT(DISTINCT{<[MonthID2]={">=$(vPrevMonth5ID)<=$(vCurrMonthID)"},$(vMetric%)={'ON TIME'}>}$(vMetric%))+
COUNT(DISTINCT{<[MonthID2]={">=$(vPrevMonth5ID)<=$(vCurrMonthID)"},$(vMetric%)={'ON TIME'}>}$(vMetric%)))

Re: SET ANALYSIS USING VARIABLE AS FIELD IN CONDITION

uacg0009 — Sun, 31 Mar 2019 13:57:53 GMT

Hi Maudifer,
Because I don't have your sample data, but the key to using the variables in set analysis is to keep the data type be same.
In your case, you need to make sure that the data type of MonthID2 is same with your 2 variables:vPrevMonth5ID, vCurrMonthID.
If not, you can use function to change your data type of your 2 variables.
Please try.
Aiolos Zhao

Re: SET ANALYSIS USING VARIABLE AS FIELD IN CONDITION

Vegar — Sun, 31 Mar 2019 15:55:52 GMT

Maybe it is the whitespace of you field name that is causing you trouble?

Try:
COUNT(DISTINCT{<[MonthID2]={">=$(vPrevMonth5ID)<=$(vCurrMonthID)"},[$(vMetric%)] = {'ON TIME'}>}[$(vMetric%)])/
(COUNT(DISTINCT{<[MonthID2]={">=$(vPrevMonth5ID)<=$(vCurrMonthID)"},[$(vMetric%)]={'ON TIME'}>}[$(vMetric%)])+
COUNT(DISTINCT{<[MonthID2]={">=$(vPrevMonth5ID)<=$(vCurrMonthID)"},[$(vMetric%)]={'ON TIME'}>}[$(vMetric%)]))

Re: SET ANALYSIS USING VARIABLE AS FIELD IN CONDITION

pradosh_thakur — Mon, 01 Apr 2019 05:21:04 GMT

hi @maudifer

As @Vegar pointed out the white space might be causing the issue, if not then you can try something similar to below ans check if it works for you

='sum({<' & [$(variable)] & '={1,2,3}>} field)'

Re: SET ANALYSIS USING VARIABLE AS FIELD IN CONDITION

maudifer — Mon, 01 Apr 2019 07:48:31 GMT

Hi Guys,

thanks you all for your suggestions, but unfortunately nothing worked.

I put below the full correct expression I need. With just the normal field, it works fine, but when i switch the normal field with a variable, it does not work.I also attached screen shot of the 2 expressions in set analysis, so you can see what gets line in red there. Basically it seems to not like the value condition. You can see below the definition of the variable too.

All the fields's values are either 'ON TIME' OR 'LATE'.

Any other idea on how to fix it ?

Expression with normal field:

COUNT(DISTINCT{<[MonthID2]={'>=$(vPrevMonth5ID)<=$(vCurrMonthID)'},[FR VS ATD]={'ON TIME'}>}[Bill of Lading (BOL)])/
(COUNT(DISTINCT{<[MonthID2]={'>=$(vPrevMonth5ID)<=$(vCurrMonthID)'},[FR VS ATD]={'ON TIME'}>}[Bill of Lading (BOL)])+
COUNT(DISTINCT{<[MonthID2]={'>=$(vPrevMonth5ID)<=$(vCurrMonthID)'},[FR VS ATD]={'LATE'}>}[Bill of Lading (BOL)]))

Expression with Variable field:

COUNT(DISTINCT{<[MonthID2]={'>=$(vPrevMonth5ID)<=$(vCurrMonthID)'},$(vMetric%)={'ON TIME'}>}[Bill of Lading (BOL)])/
(COUNT(DISTINCT{<[MonthID2]={'>=$(vPrevMonth5ID)<=$(vCurrMonthID)'},$(vMetric%)={'ON TIME'}>}[Bill of Lading (BOL)])+
COUNT(DISTINCT{<[MonthID2]={'>=$(vPrevMonth5ID)<=$(vCurrMonthID)'},$(vMetric%)={'LATE'}>}[Bill of Lading (BOL)]))

Variable ($(vMetric%)) definition:

if(Metric%='FR VS ATD',[FR VS ATD],
if(Metric%='TT PORT TO PORT', [TT PORT TO PORT],

if(Metric%='ATA TO CC', [ATA TO CC],

if(Metric%='CC TO DL', [CC TO DL],

if(Metric%='TT DOOR TO DOOR', [TT PORT TO PORT],

if(Metric%='BD TO DL', [BD TO DL]
))))))

Re: SET ANALYSIS USING VARIABLE AS FIELD IN CONDITION

pradosh_thakur — Mon, 01 Apr 2019 09:12:34 GMT

COUNT(DISTINCT{<[MonthID2]={'>=$(vPrevMonth5ID)<=$(vCurrMonthID)'},$(=vMetric%)={'ON TIME'}>}[Bill of Lading (BOL)])/
(COUNT(DISTINCT{<[MonthID2]={'>=$(vPrevMonth5ID)<=$(vCurrMonthID)'},$(=vMetric%)={'ON TIME'}>}[Bill of Lading (BOL)])+
COUNT(DISTINCT{<[MonthID2]={'>=$(vPrevMonth5ID)<=$(vCurrMonthID)'},$(=vMetric%)={'LATE'}>}[Bill of Lading (BOL)]))

Try the above . I hpe it works with the variable you have

if that doesn't work then use your expression and change your variable to the below . notice the '=' i used in the starting , don't omit that.

=if(Metric%='FR VS ATD',[FR VS ATD],
if(Metric%='TT PORT TO PORT', [TT PORT TO PORT],

if(Metric%='ATA TO CC', [ATA TO CC],

if(Metric%='CC TO DL', [CC TO DL],

if(Metric%='TT DOOR TO DOOR', [TT PORT TO PORT],


if(Metric%='BD TO DL', [BD TO DL]
))))))