Rangesum and set analysis

[#1]

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)))

[Anil_Babu_Samineni]

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

[Anil_Babu_Samineni]

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)))

[#4]

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

[Anil_Babu_Samineni]

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

[#6]

Unfortunately I cannot share the application.

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

Regards

Miguel

[sunny_talwar · Accepted solution]

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)))

[#8]

Hi Sunny

Great! ... that worked!

Many thanks

Miguel