topic Re: GetFieldSelections within date range in QlikView

GetFieldSelections within date range

Anonymous — Wed, 14 Jun 2017 10:01:37 GMT

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)

Re: GetFieldSelections within date range

Anil_Babu_Samineni — Wed, 14 Jun 2017 10:09:18 GMT

Then, I am not sure the use of Pipe( | ) symbol this case, May be try this? I just remove the symbol in syntactically

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

Re: GetFieldSelections within date range

Anonymous — Wed, 14 Jun 2017 10:18:38 GMT

Hi Anil,

Still the same result, Getting all values irrespective of From and To date range.

I need data within the date range, Is there any alternate way I can use?

Re: GetFieldSelections within date range

Anil_Babu_Samineni — Wed, 14 Jun 2017 10:21:29 GMT

The above condition what i gave is If we selected from WC_Claims value called Indemnity, Then we have to show first condition. Just use this and please select Indemnity from WC_Claims. Whether it is working or not.

=If(GetFieldSelections(WC_Claims) = 'Indemnity',

sum({<ACCOUNTING_MONTH={'>=$(=GetFieldSelections(From_Accounting_Month))<=$(=GetFieldSelections(To_Accounting_Month))'}>} [INDEMNITY]))

Re: GetFieldSelections within date range

Anonymous — Wed, 14 Jun 2017 10:45:15 GMT

Yes Anil, When I bring this condition(Indemnity alone and Full condition) in expression and enable the column, I'm seeing data for all month's, not just values within the range.

same happening upon Indemnity option selected.

Re: GetFieldSelections within date range

Anil_Babu_Samineni — Wed, 14 Jun 2017 10:47:51 GMT

Would you able to provide the MOC for testing?

Re: GetFieldSelections within date range

Anonymous — Wed, 14 Jun 2017 10:51:58 GMT

Security guidelines restrict me to upload file with sample data, I will prepare the data in-line and provide.

Re: GetFieldSelections within date range

Kushal_Chawda — Wed, 14 Jun 2017 10:52:28 GMT

try below

sum({<ACCOUNTING_MONTH={'>=$(=max(From_Accounting_Month))<=$(=max(To_Accounting_Month))'}>} [INDEMNITY])

Re: GetFieldSelections within date range

Anonymous — Wed, 14 Jun 2017 11:06:42 GMT

Hi Kushal,

Still the same result, Getting all month's values upon enabling the column and Indemnity option selection

Re: GetFieldSelections within date range

Kushal_Chawda — Wed, 14 Jun 2017 11:13:38 GMT

what is the format of ACCOUNTING_MONTH,From_Accounting_Month and To_Accounting_Month are all these fields in proper format. Take all these fields in list box check they are right aligned. if so they are in proper format

Re: GetFieldSelections within date range

sunny_talwar — Wed, 14 Jun 2017 11:25:50 GMT

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

Re: GetFieldSelections within date range

Anonymous — Fri, 16 Jun 2017 07:17:16 GMT

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 ?

Re: GetFieldSelections within date range

Kushal_Chawda — Fri, 16 Jun 2017 07:23:51 GMT

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

Re: GetFieldSelections within date range

Anonymous — Fri, 16 Jun 2017 07:30:26 GMT

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.

Re: GetFieldSelections within date range

Anil_Babu_Samineni — Fri, 16 Jun 2017 08:34:37 GMT

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?

Re: GetFieldSelections within date range

sunny_talwar — Fri, 16 Jun 2017 08:38:56 GMT

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

Re: GetFieldSelections within date range

Anonymous — Fri, 16 Jun 2017 09:23:56 GMT

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;

Re: GetFieldSelections within date range

Anonymous — Fri, 16 Jun 2017 09:42:47 GMT

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.

Re: GetFieldSelections within date range

Anil_Babu_Samineni — Fri, 16 Jun 2017 09:47:08 GMT

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

Re: GetFieldSelections within date range

Anonymous — Fri, 16 Jun 2017 10:03:02 GMT

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.