topic Re: Rangesum and set analysis in QlikView

Rangesum and set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Dear QV gurus

I'm fairly new to set analysis.

I'm trying to present cumulative project profit values for different companies in a combo chart.

I have companies using different currencies.

In the code below you can see how I am evaluating data for 3 companies NPC-UK, NPS-UK and NPC-IE.

If I evaluate only NPC-UK and NPS-UK the expression works.

If I add a third company to the expression (NPC-IE) with a currency exchange expression the syntax of the expression seems OK but I get the message "No data to display". What am I doing wrong in my code?

Please advise.

Many thanks

Miguel

=rangesum(Above(Total sum({$< COMPANY={'NPC-UK'},ACCOUNTING_YEAR={'$(vMaxYear)'},ACCOUNTING_PERIOD={'<=$(vPriorMonth)'}>} RECOVERY_MTH), 0, RowNo(Total)))

rangesum(Above(Total sum({$< COMPANY={'NPS-UK'},ACCOUNTING_YEAR={'$(vMaxYear)'},ACCOUNTING_PERIOD={'<=$(vPriorMonth)'}>} RECOVERY_MTH), 0, RowNo(Total)))

rangesum(Above(Total sum(({$<COMPANY={'NPC-IE'},ACCOUNTING_YEAR={'$(vMaxYear)'},ACCOUNTING_PERIOD={'<=$(vPriorMonth)'}>} RECOVERY_MTH)*avg({$<CURRENCY_CODE_CONSOLIDATE={'EUR'}>} CURRENCY_RATE_CONSOLIDATE)), 0, RowNo(Total)))

Re: Rangesum and set analysis

Anil_Babu_Samineni — Mon, 19 Sep 2016 17:57:20 GMT

What are you expecting for O/P, Would you provide sample which you did work around.

Re: Rangesum and set analysis

Anil_Babu_Samineni — Mon, 19 Sep 2016 18:00:38 GMT

How About this

=rangesum(Above(sum({$< COMPANY={'NPC-UK','NPS-UK','NPC-IE'},ACCOUNTING_YEAR={'$(vMaxYear)'},ACCOUNTING_PERIOD={'<=$(vPriorMonth)'}>} TOTAL RECOVERY_MTH), 0, RowNo(Total)))

* avg({$<CURRENCY_CODE_CONSOLIDATE={'EUR'}>} CURRENCY_RATE_CONSOLIDATE)), 0, RowNo(Total)))

Re: Rangesum and set analysis

Mon, 19 Sep 2016 18:20:07 GMT

Hi Anil,

I tried your expression and it doesn't work. I has syntax errors.

Please see attached file, which includes the results when only considering the evaluation of rangesum for NPC-UK and NPS-UK (only the first 4 lines on the script)

Regards

Miguel

Re: Rangesum and set analysis

Anil_Babu_Samineni — Mon, 19 Sep 2016 18:40:26 GMT

Syntax error means, would you please share the screen which you attempt and if possible can you please share the application

Re: Rangesum and set analysis

Mon, 19 Sep 2016 19:28:08 GMT

Unfortunately I cannot share the application.

Please see attached file in relation to error message in the syntax of your expression.

Regards

Miguel

Re: Rangesum and set analysis

sunny_talwar — Tue, 20 Sep 2016 01:44:27 GMT

How about this:

=RangeSum(Above(TOTAL Sum({$<COMPANY = {'NPC-UK'}, ACCOUNTING_YEAR ={'$(vMaxYear)'}, ACCOUNTING_PERIOD = {'<=$(vPriorMonth)'}>} RECOVERY_MTH), 0, RowNo(TOTAL)))

RangeSum(Above(TOTAL Sum({$< COMPANY = {'NPS-UK'}, ACCOUNTING_YEAR ={'$(vMaxYear)'}, ACCOUNTING_PERIOD = {'<=$(vPriorMonth)'}>} RECOVERY_MTH), 0, RowNo(TOTAL)))

RangeSum(Above(TOTAL Sum({$<COMPANY={'NPC-IE'}, ACCOUNTING_YEAR={'$(vMaxYear)'}, ACCOUNTING_PERIOD = {'<=$(vPriorMonth)'}>} RECOVERY_MTH) * Avg({$<CURRENCY_CODE_CONSOLIDATE ={'EUR'}>} CURRENCY_RATE_CONSOLIDATE), 0, RowNo(TOTAL)))

Changed the third RangeSum by removing extra parenthesis (can be seen in red below)

RangeSum(Above(TOTAL Sum(({$<COMPANY={'NPC-IE'}, ACCOUNTING_YEAR = {'$(vMaxYear)'}, ACCOUNTING_PERIOD ={'<=$(vPriorMonth)'}>} RECOVERY_MTH) * Avg({$<CURRENCY_CODE_CONSOLIDATE = {'EUR'}>} CURRENCY_RATE_CONSOLIDATE)), 0, RowNo(TOTAL)))

Re: Rangesum and set analysis

Tue, 20 Sep 2016 15:56:20 GMT

Hi Sunny

Great! ... that worked!

Many thanks

Miguel