Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lweny
New Contributor

YTD as KPI

Hi all, I have a requirement here which need me to create a YTD and MAT as KPI. YTD is counted from the beginning of the current year (Jan) until current month, and MAT is counted from current month to the last year of current month (for example now is May, so it will be counted from May 2019 to May 2018, total 12 months). This KPI will affect the result of total sales displayed. Anyone has an idea how to achieve this? And also, do I need to connect this KPI to the Sales dimension? Please help! Thank you in advance
13 Replies
Partner
Partner

Re: YTD as KPI

Assuming you want to calculate SUM(Amount) and that your date field is named Date and that you have other calendar fields such as YearMonth, Month, Week and Year.

YTD

=count({<
   YearMonth, 
   Month,  
   Week, 
   Year,
   Date={">=$(=YearStart(today())) <=$(=monthend(max(Date)))"}
>} Date)

MAT

=count({<
   YearMonth,
   Year,
   Month,
   Week, 
   Date={">$(=addyears(monthend(max(Date)),-1)) <=$(=monthend(max(Date)))"}
>} Date)

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
lweny
New Contributor

Re: YTD as KPI

Hi Vegar, thanks so much for replying! Apologize I forgot to mention, here I have a field name Month which contain months from 1 to 12, and Type field which contains "2017 Actual, 2018 Target, etc". That's all no other Date fields
lweny
New Contributor

Re: YTD as KPI

So, do you have another solution please?
Partner
Partner

Re: YTD as KPI

I would suggest you to create some kind of date field from the month and the first part of Type. It will be very helpful for your MAT calculation.

Maybe you can do something like this:

Load
...
makedate(Subfield(Type,' ', 1), Month) as YearMonthDate,
...
FROM...
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Sameer9585
Contributor II

Re: YTD as KPI

YTD:

Sum({<Year=,Month=,OrderDate ={"$(='>=' & Date(YearStart(Max(OrderDate)),'MM/DD/YYYY') & '<=' & Date(Max(OrderDate),'MM/DD/YYYY'))"}>}Sales)

 

This should work

Partner
Partner

Re: YTD as KPI

@Sameer9585: As I understand @lweny does not have a date field in his datamodel.

He will be able to do YTD calculation using the month field as long he is selecting the right type (containing year), but it is a bit trickier getting a rolling 12 value without some kind of yearMonth or date field.

Could you @lweny please provide some kind of sample data/application to help us understand your situation. Please let us know if it is possible to do changes in the data model or if you are limited to front end design/expressions.
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
lweny
New Contributor

Re: YTD as KPI

Hi @Vegar: here I attached the sample data for your reference. Month and Year from Type field to sum the Sales Amount.
Sameer9585
Contributor II

Re: YTD as KPI

Yeah provide any sort of document so that we can figure it out ##- Please
type your reply above this line -##
Partner
Partner

Re: YTD as KPI

Try the attached app. 

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes