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)
My last option, Can you use aggregation around your Sum() expression?
=If(GetFieldSelections(WC_Claims) = 'Indemnity',
Aggr(sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} [INDEMNITY]), WC_Claims),
If(GetFieldSelections(WC_Claims) = 'Medical',
Aggr(sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} [MEDICAL]),WC_Claims),
Aggr(sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} GROSS_AMOUNT),WC_Claims))
Values are coming upon WC_Claims filter selections, but values are not within From and To month range.
The moment I enable the column, I'm getting all month's values.
have you tried this expression SR13?
sum({<ACCOUNTING_MONTH={'>=$(=date(max(From_Accounting_Month),'MMM-YYYY'))<=$(=date(max(To_Accounting_Month),'MMM-YYYY'))'}>} [INDEMNITY])
Yes. If I directly use the expression, it is working correctly. Giving within From and To month range values.
sum({<ACCOUNTING_MONTH={'>=$(=date(max(From_Accounting_Month),'MMM-YYYY'))<=$(=date(max(To_Accounting_Month),'MMM-YYYY'))'}>} [INDEMNITY])
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.
I wonder why QV SR13 is behaving like this. same is working correctly on SR5 & SR6.
try to exclude the WC_Claims filter in expression
sum({<WC_Claims ,ACCOUNTING_MONTH={'>=$(=date(max(From_Accounting_Month),'MMM-YYYY'))<=$(=date(max(To_Accounting_Month),'MMM-YYYY'))'}>} [INDEMNITY])
I have to display data based on WC_Claims filter selection within from and To month range.
If you can share your sample file it will give you quick answer
Try adding a monthstart
Data:
LOAD
RowNo() AS RowNo,
Date(MonthStart(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;