Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a filter 'WC_Claims', based on the option selected on filter I am displaying INDEMNITY, MEDICAL & GROSS_AMOUNT values using
GetFieldSelections within year range (start and end year taken into variables Eg. 2015 to 2016) correctly as below.
=If(GetFieldSelections(WC_Claims, '|') = 'Indemnity',
sum({<YEAR={'>=$(=vStartYear)<=$(=vEndYear)'}>}[INDEMNITY]),
If(GetFieldSelections(WC_Claims, '|') = 'Medical',
sum({<YEAR={'>=$(=vStartYear)<=$(=vEndYear)'}>} [MEDICAL]),
sum({<YEAR={'>=$(=vStartYear)<=$(=vEndYear)'}>} GROSS_AMOUNT))
When I am trying to simulate the same logic for Month range (From and To Accounting month taken from multi-box filters Eg. Mar-2015 to Mar-2016) using below approach. it is not working. Getting all month's values irrespective of From and To month range.
=If(GetFieldSelections(WC_Claims, '|') = 'Indemnity',
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} [INDEMNITY]),
If(GetFieldSelections(WC_Claims, '|') = 'Medical',
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} [MEDICAL]),
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} GROSS_AMOUNT))
I am not sure what going wrong. Can someone pls help me. Thanks in Advance.
Note: If I directly use the below expression, it is working correctly
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} GROSS_AMOUNT)
Is there a format difference between ACCOUNTING_MONTH and From_Accounting_Month and To_Accounting_Month
Also, you can try to simplify your 1st expression to this
Sum({<YEAR={'>=$(=vStartYear)<=$(=vEndYear)'}>} $(=Upper(GetFieldSelections(WC_Claims)))
There is no format difference between ACCOUNTING_MONTH and From_Accounting_Month and To_Accounting_Month.
The expression which I am using is working on 11.2 SR5 perfectly with filter selections, but not working on 11.2 SR13(installed version on my machine).
What changes should I do to make the same expression work on my machine ?
try to give format of ACCOUNTING_MONTH to From_Accounting_Month & To_Accounting_Month
sum({<ACCOUNTING_MONTH={'>=$(=date(max(From_Accounting_Month),'MMM-YYYY'))<=$(=date(max(To_Accounting_Month),'MMM-YYYY'))'}>} [INDEMNITY])
Note: I have assumed MMM-YYYY, give actual format
Yes. The format of ACCOUNTING_MONTH, From_Accounting_Month & To_Accounting_Month are all same 'MMM-YYYY'.
Note: If I directly use the below expression, it is working correctly.
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} GROSS_AMOUNT)
The below whole expression is not working on SR13, but working on SR5 & 6.
=If(GetFieldSelections(WC_Claims) = 'Indemnity',
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} [INDEMNITY]),
If(GetFieldSelections(WC_Claims) = 'Medical',
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} [MEDICAL]),
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} GROSS_AMOUNT))
Should I have include any additional parameter in GetFieldSelections to make it work for SR13.
Can you try with Match function.
=If(Match(GetFieldSelections(WC_Claims) = 'Indemnity',WC_Claims),
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} [INDEMNITY]),
If(Match(GetFieldSelections(WC_Claims) = 'Medical',WC_Claims),
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} [MEDICAL]),
sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} GROSS_AMOUNT))
PS - Here, do you select any value from WC_Claims?
Would you be able to share the script lines for the three dates field in the script?
ACCOUNTING_MONTH, From_Accounting_Month and To_Accounting_Month
Data:
LOAD
RowNo() AS RowNo,
Date(ACCOUNTING_MONTH, 'MMM-YYYY') AS ACCOUNTING_MONTH
FROM [Data.qvd](qvd);
Accounting_Start_Month:
LOAD
Distinct(Date(ACCOUNTING_MONTH, 'MMM-YYYY')) as From_Accounting_Month
Resident Data;
Accounting_End_Month:
LOAD
distinct(Date(ACCOUNTING_MONTH, 'MMM-YYYY')) as To_Accounting_Month
Resident Data;
Hi Anil,
Yes I select values from WC_Claims filter. WC_Claims Filter selection value is coming correctly from initial time, but the problem is values are not coming within the From and To month range selected using GetFieldSelections.
I am not sure what's wrong with SR13, my expressions are working correctly on SR 5 and SR6 systems.
Is there any alternate function to GetFieldSelections to select the date/month values from listbox/multibox filters ?
Production access point server is SR13.
But, As you mentioned you got the result when you use plane aggregation with set analysis. The better approach if you provide sample application to look the behavior. I am not sure what was the cause over there. If it is working that should work. Could be experts can help you
Yes If I use plane aggregation with set analysis, it is working. When I bring in WC_Claims filter into picture and select WC_Claims filter, I'm getting all month's values of WC_claims filter selection.
Expectation is to get values of WC_claims filter selection within the selected month range.
I'm in secured work location, uploading any sample data file will be risk.