Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

RangeSum/FirstSortedValue allow GetFieldSelections ?

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 ?

7 Replies
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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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