Dimension as a KPI

[mindyckay]

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

[tresesco]

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>))

[Lisa_P]

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'.

[tresesco]

Hi Lisa,

I guess Date(MonthField, 'MMM') would give wrong output.

[mindyckay · Accepted solution]

I stumbled upon the correct way to get the 'MMM' format.

..... -> change Number Formatting to 'Measure expression'