Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
amruta_j
Partner
Partner

Formula working in Text Box but not in Pivot Table

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!

 

 

1 Solution

Accepted Solutions
amruta_j
Partner
Partner
Author

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

 

View solution in original post

6 Replies
bharathadde
Creator II
Creator II

try this

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

amruta_j
Partner
Partner
Author

Tried it, not working 😞

jaibau1993
Partner
Partner

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
Creator II
Creator II

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
Partner
Partner
Author

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

 

View solution in original post