Formula working in Text Box but not in Pivot Table

[amruta_j]

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)

or

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!

[bharathadde]

try this

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

[amruta_j]

Tried it, not working 😞

[jaibau1993]

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. 

[bharathadde]

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)

[avinashelite]

Try like this 

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

or 

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

[amruta_j · Accepted solution]

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. 🙂