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)

[sunny_talwar]

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

[Anonymous]

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 ?

[Kushal_Chawda]

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

[Anonymous]

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.

[Anil_Babu_Samineni]

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?

[sunny_talwar]

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

[Anonymous]

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;

[Anonymous]

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.

[Anil_Babu_Samineni]

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

[Anonymous]

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.