topic Re: Dimension as a KPI in QlikView

Dimension as a KPI

mindyckay — Fri, 21 Sep 2018 05:19:45 GMT

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

Re: Dimension as a KPI

tresB — Fri, 21 Sep 2018 05:27:09 GMT

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

Re: Dimension as a KPI

Lisa_P — Fri, 21 Sep 2018 05:58:41 GMT

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

Re: Dimension as a KPI

tresB — Fri, 21 Sep 2018 06:06:41 GMT

Hi Lisa,

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

Re: Dimension as a KPI

mindyckay — Fri, 21 Sep 2018 07:22:18 GMT

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

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