Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

GetFieldSelections within date range

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)

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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;