topic Re: RangeSum/FirstSortedValue allow GetFieldSelections ? in QlikView

RangeSum/FirstSortedValue allow GetFieldSelections ?

Anonymous — Sat, 10 Jun 2017 08:12:13 GMT

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 ?

Re: RangeSum/FirstSortedValue allow GetFieldSelections ?

sunny_talwar — Sat, 10 Jun 2017 10:09:58 GMT

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

Re: RangeSum/FirstSortedValue allow GetFieldSelections ?

Anonymous — Sun, 11 Jun 2017 01:06:47 GMT

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.

Re: RangeSum/FirstSortedValue allow GetFieldSelections ?

sunny_talwar — Sun, 11 Jun 2017 01:09:03 GMT

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

Re: RangeSum/FirstSortedValue allow GetFieldSelections ?

Anonymous — Sun, 11 Jun 2017 01:19:10 GMT

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.

Re: RangeSum/FirstSortedValue allow GetFieldSelections ?

sunny_talwar — Sun, 11 Jun 2017 06:53:49 GMT

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

Best,

Sunny

Re: RangeSum/FirstSortedValue allow GetFieldSelections ?

Anonymous — Mon, 12 Jun 2017 05:35:36 GMT

Does RangeSum/firstsortedvalue allow GetFieldSelections ?

Please can you tell if you have used them anytime.

Re: RangeSum/FirstSortedValue allow GetFieldSelections ?

sunny_talwar — Mon, 12 Jun 2017 11:00:20 GMT

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