Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need help with codes to create KPIs in Qlik Sense.
Business Question:
Which months in the year has the highest and lowest number of patients?
- [SurgeryDate.autoCalendar.Month]
- Max(Count(distinct PatientID)) ..... correct month is September
- Min(Count(distinct PatientID)) ..... correct month is August
I tried ...
= Mode([SurgeryDate.autoCalendar.Month]), received a '9' correct number but need 'September'
=FirstSortedValue([SurgeryDate.autoCalendar.Month], Aggr(Count(distinct PatientID, [SurgeryDate.autoCalendar.Month])) , received a '8' correct number but need 'August'.
Don't know why [SurgeryDate.autoCalendar.Month] is coming back as numbers in the above calculations because in the charts as a dimension the fields are shown as Sep and Aug.
Thanks in advance,
Mindy
I stumbled upon the correct way to get the 'MMM' format.
..... -> change Number Formatting to 'Measure expression'
Usually, to get month name instead of the number, you could use MaxString()/MinString(). However, here you could also try like:
=Month(MakeDate(1,<YourExpression returnign the right number>))
Most date dimensions will have dual capability. Stored as numbers, but can be displayed as text or number.
Try formatting your expressions like this..
= Date(Mode([SurgeryDate.autoCalendar.Month]),'MMM') received a '9' correct number but need 'September'
= Date(FirstSortedValue([SurgeryDate.autoCalendar.Month], Aggr(Count(distinct PatientID, [SurgeryDate.autoCalendar.Month])) , 'MMM'), received a '8' correct number but need 'August'.
Hi Lisa,
I guess Date(MonthField, 'MMM') would give wrong output.
I stumbled upon the correct way to get the 'MMM' format.
..... -> change Number Formatting to 'Measure expression'