topic Re: Set Analysis and the NUM function in QlikView

Set Analysis and the NUM function

Anonymous — Tue, 14 Jul 2015 02:48:21 GMT

Hi there,

I am using a variable to return a result. in the variable it uses set analysis to ensure that regardless of what fiscalyear is selected the result for ALL years will show.

=NUM($(vResultAllYears),NumberFormat)

The next thing I do is use a field to determine the number format. In the instance below it should be formatted as '#.##%'.

As you can see from the table below, when a single fiscalyear (2011-12) is selected, all values are displayed but the NumberFormat is only applied to the selected range.

How can I overcome this? Keeping in mind that depending on the indicator being displayed this number format will change.

see attached sample.

Thanks,

John.

Re: Set Analysis and the NUM function

sunny_talwar — Tue, 14 Jul 2015 03:20:48 GMT

Try this expression:

=NUM($(vResultAllYears), Only({$ <FiscalYear = >}NumberFormat))

Re: Set Analysis and the NUM function

Anonymous — Tue, 14 Jul 2015 03:28:00 GMT

Hi Sunindia,

Thank you, that solution works for this situation.

Unfortunately I was being a little bit simple in my calculation in the variable vResultsAllYears and instead of looking like this:

SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)

it actually looks like this. For some reason regardless of the set analysis it only displays a result for the selected year:

IF(CalculationRule='N/D'

,SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)

,IF(CalculationRule='(N/D)/R'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)/Rate)

,IF(CalculationRule='(N/D)/100'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)/100)

,IF(CalculationRule='(N/D)/1000'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)/1000)

,IF(CalculationRule='(N/D)*R'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)*Rate)

,IF(CalculationRule='(N/D)*100'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)*100)

,IF(CalculationRule='(N/D)*1000'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)*1000)

)))))))

Would you know how to force this to display across all years?

Thank you again.

Re: Set Analysis and the NUM function

settu_periasamy — Tue, 14 Jul 2015 03:32:59 GMT

try this also

=NUM($(vResultAllYears),'$(=NumberFormat)')

Re: Set Analysis and the NUM function

Anonymous — Tue, 14 Jul 2015 03:35:03 GMT

Settu_periasamy,

that is a very interesting solution and it also works. But what is it doing?

Re: Set Analysis and the NUM function

sunny_talwar — Tue, 14 Jul 2015 03:36:11 GMT

May be this:

IF(Only({$ <FiscalYear = >} CalculationRule)='N/D'

,SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)

,IF(Only({$ <FiscalYear = >} CalculationRule) ='(N/D)/R'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)/Rate)

,IF(Only({$ <FiscalYear = >} CalculationRule) ='(N/D)/100'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)/100)

,IF(Only({$ <FiscalYear = >} CalculationRule) ='(N/D)/1000'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)/1000)

,IF(Only({$ <FiscalYear = >} CalculationRule) ='(N/D)*R'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)*Rate)

,IF(Only({$ <FiscalYear = >} CalculationRule) ='(N/D)*100'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)*100)

,IF(Only({$ <FiscalYear = >} CalculationRule) ='(N/D)*1000'

,(SUM({$ <FiscalYear = >} Numerator)/SUM({$ <FiscalYear = >} Denominator)*1000)

)))))))

Re: Set Analysis and the NUM function

settu_periasamy — Tue, 14 Jul 2015 03:40:09 GMT

yes the $ symbol will do that. 🙂

It will evaluate the inside of the value. See this if you are interested..

The Magic of Dollar Expansions

Re: Set Analysis and the NUM function

Anonymous — Tue, 14 Jul 2015 03:59:36 GMT

Its amazing. I've never seen an expression like this before.

Re: Set Analysis and the NUM function

Anonymous — Tue, 14 Jul 2015 04:04:49 GMT

Thank you both. This has been a valuable experience for me.