topic Variable with var parameters not working with set analysis in QlikView

Variable with var parameters not working with set analysis

rutger_jansen — Fri, 07 Sep 2018 13:57:02 GMT

Hi,

I'm trying to build a variable structure as follows:

vMetricLabel: An if statement that takes in two variables as metrics

vMetric: A metric that takes a set analysis variable as a parameter

vSetAnalysis: A set analysis variable for use in the metric

vMetricLabel:

If( ($1) / ($2) < 0.9, '<90%',

If( ($1) / ($2) < 1.0, '<100%',

'>100%'

))

vMetric1:

SUM( {< $1>} Profit ) / SUM( {< $1 >} Sales )

vMetric2:

SUM( {< $1, FLAG={'Y'} >} Profit ) / SUM( {< $1, FLAG={'Y'} >} Sales )

vThisMonth:

MDate = {'>=$(=Monthstart(Max(MDate)))<=$(=MonthEnd(Max(MDate)))'}

vLastMonth:

MDate = {'>=$(=MonthStart(AddMonths(Max(MDate),-1)))<=$(=MonthEnd(AddMonths(Max(MDate),-1)))'}

This works:

$(vMetricLabel( $(vMetric1( $(vThisMonth))), $(vMetric($(vLastMonth))) ))

But this doesn't:

$(vMetricLabel( $(vMetric2( $(vThisMonth))), $(vMetric($(vLastMonth))) ))

So I guess it has to do with the comma in the second metric variable.

Does anyone know how to fix this? I've added a document with sample data, variables etc. that demonstrates the problem.

Thanks

Rutger

Re: Variable with var parameters not working with set analysis

vishsaggi — Fri, 07 Sep 2018 15:25:38 GMT

May be try this?

$(vMetricLabel($(=$(vMetric2($(vThisMonth)))), $(=$(vMetric2($(vLastMonth)))) ))

Re: Variable with var parameters not working with set analysis

rutger_jansen — Fri, 07 Sep 2018 15:58:07 GMT

That didn't work but this does:

$(vMetricLabel( (=$(vMetric2( $(vThisMonth)))), (=$(vMetric2($(vLastMonth)))) ))

So thanks very much.

Re: Variable with var parameters not working with set analysis

vishsaggi — Fri, 07 Sep 2018 16:18:35 GMT

It worked for me in the same file you sent me. Not sure why it did not work at your end.

Re: Variable with var parameters not working with set analysis

rutger_jansen — Fri, 07 Sep 2018 16:26:39 GMT

I just copy/pasted your expression into my app again:

'Error: Error in expression:

')' expected' ...

I don't know either but I definitely had to leave out the additional dollars in your expression out to make it work.

Thanks again.

Re: Variable with var parameters not working with set analysis

vishsaggi — Fri, 07 Sep 2018 16:35:31 GMT

Ok no worries. You got it work. Close the thread accordingly.