Count with KPI's dates

[Dante_83]

Hello Experts,

I'm trying to use the dates in my KPI to count material. For example, the expression below works well when I want to count material between dates. The problem is that every time I refresh the data, I have to change the date in the expression.

Count({<[Dta Receive]={">=$(='4/30/2021')<=$(='7/21/2021')"}>} COG)

I have several KPI's with this expression "Sum(Aggr(Sum(distinct{<Phase={'Induction'}>} Date([Actual Start Date.autoCalendar.Date])), Phase))" that extract the date I want base on its phases.

I have tried using , "Count({<[Dta Receive]={">=$(=[Start of Phase 1 Date])<=$(=[End of Phase 1 Date])"}>} COG)". But no success. Does anyone know if this is possible? I appreciate any help you can provide.

[vinieme12]

Refer this article

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

Count({<[Dta Receive]={">=$(=Date(Min([Start of Phase 1 Date])))<=$(=Date(Max([End of Phase 1 Date])))"}>} COG)

[Dante_83]

Hello Vinieme12,

Thank you for your help. I've tried the expression and it return zero count. I get an accurate count if I use an expression Count({<[Dta Receive]={">=$(='4/30/2021')<=$(='7/21/2021')"}>} COG). But, I can't get the expression to work with the date in my KPI. The article is helpful.

[vinieme12]

 Can you paste the expression you used ?

Not the one where dates are hardcoded 

Can you paste a snapshot of the entire expression editor?on the bottom of the expression editor you can see how the expression evaluates and provides insight as to what may be wrong 

Also are  [End of Phase 1 Date] and [Start of Phase 1 Date] Are actual fields??

[Dante_83]

Hello Vinieme12,

Thank you for following up.

KPI's Date

Below is my original.

This one is a suggested Expression.

[Dante_83]

Hello Vinieme12,

Thank you for following up.

KPI's Date

Below is my original.

This one is a suggested Expression.

Thank you for your help again.

[vinieme12 · Accepted solution]

Notice the highlighted part below on the screenshot, your dates are not evaluating

1) Are [Start of Phase 1 Date] and [End of Phase 1 Date] actual fields?

2) If they are fields, what is the format of [Start of Phase 1 Date] and [End of Phase 1 Date]  ?

3) Confirm if the fields are stored as Dates and not Text by  trying these expressions in  a text box

= isnum(min([Start of Phase 1 Date] ))   << if returns 0 means that these are stored as Text, if returns -1 then Date

= isnum(min([End of Phase 1 Date] ))     << if returns 0 means that these are stored as Text, if returns -1 then Date

4) if these are Stored as Text format them to Date while loading Data in script as below

     Date#([Start of Phase 1 Date],'CurrentFormatOfTheField') as [Start of Phase 1 Date]

[Dante_83]

Hello Vineeth,

Thank you for your help! I see what my problem is now. I need to format [Start of Phase 1 Date] and [End of Phase 1 Date] to date field. I'm going to work on it this weekend. If it's ok with you, I would like to comeback again just in case I have more questions. Thank you again for your help.