Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Qlik Sense version 3,0 SR1
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?
The link is not opening up. Do you want to attach your sample file here?
Here is the sample data.
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?
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)
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)
How about this?
Count({$<BusinessDate= {"$(=Date(MonthEnd(Max(BusinessDate), -1)))"}>} MonthID)
Ok, this works, thanks so much.
Count({$<BusinessDate= {"$(=addmonths(Date(Max(BusinessDate)),-1,1))"}>} MonthID)