Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a dimension and 2 measures. The dimension is date while the measures are enrollments and checkins. Below is the expression I am using for both Enrollments and Checkins but it keeps giving me the total count and not the daily count.
=Sum({$< Date={"$(=Date(Max(Date),'MM/DD/YYYY'))"}>} [Enrollments])
=Sum({$< Date={"$(=Date(Max(Date),'MM/DD/YYYY'))"}>} [CheckIns])
Please help!!
Is the format of the date field exactly MM/DD/YYYY ?
this will not work if the Date field has a different format
Can you post a snapshot of the Date field and a snapshot of the expression editor??
Thank you for your response. Yes the format of the date field is MM/DD/YYYY. Below is a snapshot of the Date field and expression editor
2 Observations
1)The field is a timestamp field
2) You don't have a field named "Date" in your model at all
You need to create a date field using your timestamp field
create a new field as below in your script first
,Date(Floor(timestampfieldname),'MM/DD/YYYY') as Datefield
Then in Chart use
=Sum({$< Datefield ={"$(=Date(Max(Datefield ),'MM/DD/YYYY'))"}>} [Enrollments])
=Sum({$< Datefield ={"$(=Date(Max(Datefield ),'MM/DD/YYYY'))"}>} [CheckIns])
Thanks again for your reply. I am new to Qlik sense, when you say "create a new field in your script first" what do you mean since data was imported/loaded to qlik sense from an excel spreadsheet. where do I create a new field?
in the data load Editor
https://www.youtube.com/watch?v=bRR2C3Xl-PY
Thanks again. I edited my script and applied the expressions in chart. Still no change. The output in my chart is still total enrollmenmt and CheckIns not daily. I need to see daily enrollments and Checkins.
Any help please.
I think what do you want to do isn't possible with a set analysis because those conditions are applied on a column-level like a selection and not on a row-level. To get this you need an if-loop, maybe something like this:
if(Date=Max(Date), sum([Enrollments]))
- Marcus
Thanks Marcus. The expression returned an error. see below..