RangeSum/FirstSortedValue allow GetFieldSelections ?

[Anonymous]

Hi Experts,

I use this set analysis expression to display 'Gross_Amount' (directly from DB) value within 'From_Accounting_Month' and 'To_Accounting_Month' date ranges correctly.

= num(sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} Gross_Amount),'$#,##0')

Now I am using set analysis expression in column to derive New 'Gross_Amount' column.

=If(Below(TOTAL Only(TempKey)) <> Only(TempKey), RangeSum(Sum(Gross_Incr_ITD), -FirstSortedValue(TOTAL <DIVISION> Gross_Incr_ITD, ReportingDate)), Sum(Gross_Amount))

I am getting new 'Gross_Amount' column correctly.

However I want to display derived 'Gross_Amount' value within 'From_Accounting_Month' and 'To_Accounting_Month' date ranges.

I used below expression, but it is not working.

=If(Below(TOTAL Only(TempKey)) <> ONly(TempKey), RangeSum(num(Sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} Gross_Incr_ITD),'$#,##0'), -FirstSortedValue(TOTAL <DIVISION> Gross_Incr_ITD, ReportingDate)),

num(Sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} Gross_Amount),'$#,##0'))

Does RangeSum/FirstSortedValue allow GetFieldSelections ? Any alternative expression ?

[sunny_talwar]

May be try this

=If(Below(TOTAL Only({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>}TempKey)) <> Only({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>}TempKey), RangeSum(Sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>}Gross_Incr_ITD), -FirstSortedValue({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>}TOTAL <DIVISION> Gross_Incr_ITD, ReportingDate)), Sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>}Gross_Amount))

[Anonymous]

Hi Sunny,

It is not working. I just get NULL values for all columns in single row.

I cannot upload qvw due to environment restrictions.

[sunny_talwar]

‌Not working doesn't tell much. Do you have more information as to what is going wrong? May be share a sample file?

[Anonymous]

Screenshot with actual data ( all dimensions) after change.

Earlier I used to get data like below.

Couldn't upload any file due to security guidelines.

[sunny_talwar]

Can't seem to find a reason why this may not be working.... may be someone else can.

Best,

Sunny

[Anonymous]

Does RangeSum/firstsortedvalue allow GetFieldSelections ?

Please can you tell if you have used them anytime.

[sunny_talwar]

I never used them but I don't see why they cannot be used with set analysis.... there is nothing specific to RangeSum or FirstSortedValue which wouldn't allow the use of set analysis within those functions