topic Re: Formula working in Text Box but not in Pivot Table in QlikView

Formula working in Text Box but not in Pivot Table

amruta_j — Sat, 16 Nov 2024 20:45:31 GMT

Hi All, yet again need your expertise on below issue:

I'm calculating a value in textbox with below formula:

=count(DISTINCT{<Date_Application_Received={">=$(=monthstart(addmonths(max(date_accepted_by_customer),-1)))<=$(=monthend(addmonths(max(date_accepted_by_customer),6)))"}>}Policy_No)

count(distinct{<Date_Application_Received={">=$(vFirstDate)<=$(vLastDate)"}>}Policy_No),

vFirstDate =monthstart(addmonths(max(date_accepted_by_customer),-1))

vLastDate=monthend(addmonths(max(date_accepted_by_customer),6))

which is working perfectly fine in textbox.

As soon as I'm replicating this formula in Pivot table, values are not coming as expected. But when I'm selecting MonthYear filter, pivot table is showing value for selected filter. Attached the screenshots.

So my deduction is formula is working fine, but I'm missing some more calculations in order to work in pivot table, not sure about this part.

Can you please help me to figure this out.

Thank You!

Re: Formula working in Text Box but not in Pivot Table

bharathadde — Thu, 30 May 2019 20:14:12 GMT

try this

count(distinct{<Date_Application_Received={">=$(=vFirstDate)<=$(=vLastDate)"}>}Policy_No),

Re: Formula working in Text Box but not in Pivot Table

amruta_j — Fri, 31 May 2019 01:55:14 GMT

Tried it, not working 😞

Re: Formula working in Text Box but not in Pivot Table

jaibau1993 — Fri, 31 May 2019 06:54:29 GMT

Hi! Set Analysis expression are calculated one time per chart. That means that values of MonthYear dimension are not taken into account in vFirstDate and vLastDate. When no selections made, vLastDate and vFirstDate are calculated for all possible values of MonthYear. When you select Jan-2018, the variables are calculated for all possible values given that selection.

I suggest you to use an if statement

count(distinct if("Conditions on Dates", Policy_No))

Regards,

Jaime.

Re: Formula working in Text Box but not in Pivot Table

bharathadde — Fri, 31 May 2019 16:40:41 GMT

Please try this but only use one Quote(') instead of using two quotes (") like below

=count(DISTINCT{<Date_Application_Received={'>=$(=monthstart(addmonths(max(date_accepted_by_customer),-1)))<=$(=monthend(addmonths(max(date_accepted_by_customer),6)))'}>}Policy_No)

Re: Formula working in Text Box but not in Pivot Table

avinashelite — Tue, 04 Jun 2019 06:17:21 GMT

Try like this

count(distinct{<Date_Application_Received={">='$(vFirstDate)'<='$(vLastDate)'"}>}Policy_No)

count(distinct{<Date_Application_Received={">=$(=vFirstDate)<=$(=vLastDate)"}>}Policy_No)

Re: Formula working in Text Box but not in Pivot Table

amruta_j — Thu, 06 Jun 2019 10:29:56 GMT

Hi All,

Thanks for all the suggestions. But what worked in this case is given below:

I've created same variables in the backend script with group by clause.

Load date_accepted_by_customer,
monthstart(addmonths(max(date_accepted_by_customer),-1)) as FirstDate,
monthend(addmonths(max(date_accepted_by_customer),6)) as LastDate
Resident MPD_TXN
group by date_accepted_by_customer;

and then in the frontend used below formula:

count(distinct if(Date_Application_Received >= FirstDate and Date_Application_Received <= LastDate,Policy_No))

This worked well. 🙂