Qlik Sense Default to a date not working

[#1]

I have a statement that i can never get to work with a date field.  It is below:

Count({$<[BusinessDate]= {'$(=If(GetSelectedCount([BusinessDate]) < 1, Max([BusinessDate]), [BusinessDate]))'}>} MemberNbr)

I am trying to get a count of MemberNbrs for the month and set the max BusinessDate as the default if there are no selections made.  If i make a selection then i get the results i would expect.  If i select the max date on a chart then i get the count i want,  but if there is no selection then my KPI shows 0.

It works fine if the BusinessDate is a text field.  However when the BusinessDate is a date field then the KPI returns 0.  Any ideas what i am doing wrong?

[#2]

Qlik Sense version 3,0 SR1

[#3]

https://community.qlik.com/docs/DOC-17707

I attached a sample file that replicates the problem here.

Here is a sample file of data, if i load this in and do the following statement in a KPI, then i get the result 71,746 which represents the count of MonthID for the max MonthID.

Count({$<[MonthID]= {$(=If(GetSelectedCount([MonthID]) = 0, max([MonthID]), [MonthID]))}>} MonthID)

However if i change the MonthID to BusinessDate then i get 0. 

Count({$<[BusinessDate]= {$(=If(GetSelectedCount([BusinessDate]) = 0, max([BusinessDate]), [BusinessDate]))}>} MonthID)

This is the problem i am trying to fix.  In the end what i want to do is have one KPI that says here is the count for the max BusinessDate, and then have a second KPI that shows the count for last months BusinessDate.  Does that make sense?

[sunny_talwar]

The link is not opening up. Do you want to attach your sample file here?

Uploading a Sample

[#5]

Here is the sample data.

[#6]

Here is a sample file of data, if i load this in and do the following statement in a KPI, then i get the result 71,746 which represents the count of MonthID for the max MonthID.

Count({$<[MonthID]= {$(=If(GetSelectedCount([MonthID]) = 0, max([MonthID]), [MonthID]))}>} MonthID)

However if i change the MonthID to BusinessDate then i get 0. 

Count({$<[BusinessDate]= {$(=If(GetSelectedCount([BusinessDate]) = 0, max([BusinessDate]), [BusinessDate]))}>} MonthID)

This is the problem i am trying to fix.  In the end what i want to do is have one KPI that says here is the count for the max BusinessDate, and then have a second KPI that shows the count for last months BusinessDate.  Does that make sense?

[sunny_talwar · Accepted solution]

Try this script:

Table:

LOAD

    MembershipMonthlySKey,

    Date(MonthStart(Date#(MonthID, 'YYYYMM')), 'YYYYMM') as MonthID,

    Date(BusinessDate) as BusinessDate

FROM [lib://Lib/TestData.xlsx]

(ooxml, embedded labels, table is Sheet1);

and these two expressions should work for you

Count({$<[MonthID]= {"$(=Date(Max([MonthID]), 'YYYYMM'))"}>} MonthID)

Count({$<BusinessDate= {"$(=Date(Max(BusinessDate)))"}>} MonthID)

[#8]

So i can get those to work, now what i need to to subtract a month from the max().  I tried this and it does not work, any thoughts?

Count({$<BusinessDate= {"$(=Date(addmonths(Max(BusinessDate),-1)))"}>} MonthID)

[sunny_talwar]

How about this?

Count({$<BusinessDate= {"$(=Date(MonthEnd(Max(BusinessDate), -1)))"}>} MonthID)

[#10]

Ok, this works, thanks so much.

Count({$<BusinessDate= {"$(=addmonths(Date(Max(BusinessDate)),-1,1))"}>} MonthID)