Exchanging today() with an existing field

[Daniel_K]

Hello,

I currently have an expression that works but it needs slightly amending to provide me with a more accurate figure but the problem I've encountered is that I can't simply replace today() with a data field, presumably due to the syntax..... please can you let me know where I'm going wrong.

Basically what I'm trying to do is change the expression so that it works out if the last time an actual reading was used was over 90 days ago based on the last time we issued an invoice rather than todays date.

Current expression which isn't technically correct

=count({<[Smart Meter]={'YES'}, [Date an actual reading was last used]={"<$(=today()-90)"}>}distinct Supply ID)

My attempt at changing the expression, but it comes up with an error.

=count({<[Smart Meter]={'YES'}, [Date an actual reading was last used]={"<$(=[Latest Billed Date]-90)"}>}distinct Supply ID)

[sunny_talwar]

May be this

=Count({<[Smart Meter] = {'YES'}, [Date an actual reading was last used] = {"<$(=Max([Latest Billed Date])-90)"}>} DISTINCT [Supply ID])

or

=Count({<[Smart Meter] = {'YES'}, [Date an actual reading was last used] = {"<$(=Date(Max([Latest Billed Date])-90, 'DateFormatHere'))"}>} DISTINCT [Supply ID]) 

Make sure to replace DateFormatHere in the second expression with the date format for Date an actual reading was last used field.

[Daniel_K]

Good Morning Sunny,

The formula hasn't quite worked, please find attached example data showing my findings within a chart which show what I'm expecting the result of this expression to be.

On the second sheet of the example attached are the 5 results the current expressions you provided brings back.

Basically as it stands I have the backing data but I'm trying to create a summary page which is where the problem is occurring.

[johnca]

You need to make sure [Latest Billed Date] and [Date an actual meter reading was last used] are the same numeric format. Right now it appears your [Latest Billed Date] is text.

Start with Date(Date#([Latest Billed Date],'DD/MM/YYYY'),'DD/MM/YYYY') as [Latest Billed Date]

Or quite possibly convert all dates to numbers.

Num(Date#([Latest Billed Date],'DD/MM/YYYY')) as [Latest Billed Date]

Num(Date#([Date an actual meter reading was last used],'DD/MM/YYYY')) as [Date an actual meter reading was last used]

HTH,

John

[Brett_Bleess]

Daniel, did John's post help you get things working properly?  If so, do not forget to return to the thread and use the Accept as Solution button on John's post to give him credit for the help in getting things working properly and to let other Community Members know that was the actual issue.  If you are still working on things and need further help, leave an update.

Here is a Design Blog link to Date functions that might come in handy in the future, and I am also including the base URL for the area after that, so you can search going forward in that area, as there are hundreds of how-to posts in there from our internal experts that may help you on future projects, it is a good resource to find ideas.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry, slipped in an extra link for you on dates specifically.

Regards,
Brett

[Daniel_K]

Hello john,

Thnk you for the suggestions, I've updated my script as recommended but there is no difference i nthe results. What I have noticed though is that when I type out the formula it doesn't come up with suggested fields to use for this part of the formula "<$(=Date(Max([Latest Billed Date2])-90, 'DD/MM/YYYY'))" making me think there might be an issue with the syntax.

I would expect the word max to be blue but it is red.....

Thanks for your help so far.

[Brett_Bleess]

While you are waiting on John, have a look at the following Design Blog post, there is an example in there that is close to yours, that might help somewhat:

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

Regards,
Brett