GetFieldSelections within date range

[Anonymous]

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)

[Anil_Babu_Samineni]

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

[Anonymous]

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.

[Kushal_Chawda]

have you tried this expression SR13?

sum({<ACCOUNTING_MONTH={'>=$(=date(max(From_Accounting_Month),'MMM-YYYY'))<=$(=date(max(To_Accounting_Month),'MMM-YYYY'))'}>} [INDEMNITY])

[Anonymous]

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.

[Kushal_Chawda]

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

[Anonymous]

I have to display data based on WC_Claims filter selection within from and To month range.

[Kushal_Chawda]

If you can share your sample file it will give you quick answer

[sunny_talwar]

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;