topic Re: KPI for last quarter in App Development

KPI for last quarter

alexanderdafflitto — Thu, 23 Jul 2020 07:20:27 GMT

Hello,

I want to add a KPI or Multi KPI chart to display a measure for the last quarter only. I manage to use quarter as dimension in the multi KPI but then I get all quarters, I want only the last. How can I solve this?

Re: KPI for last quarter

Vegar — Thu, 23 Jul 2020 08:25:29 GMT

What is your definition of last quarter?

If you where to look today, July 23, are you expecting to see Q2 (Apr-Jun) or are you expecting Q3 (Jul-Sep)?

Should the last quarter change dynamicly based on periods in your selections or should it only be locked to the current last quarter (using today as base)?

Re: KPI for last quarter

alexanderdafflitto — Thu, 23 Jul 2020 08:35:22 GMT

Hello,

today I would like to see the quarter Apr - Jun, displayed as Q2-2020 for example. It would not be changed by filter, always fixed to the last quarter.

thank you.

Re: KPI for last quarter

sugisense — Thu, 23 Jul 2020 08:35:45 GMT

Hi @alexanderdafflitto

Please clarify, whether you wants to display the present quarter of the selected date or the last quarter of the year(Q4).

1. For Present Quarter:

Try using the set as:

Ex - sum({<Date={">=$(=QuarterStart(Max(Date)))<=$(=Max(Date))"}>}Sales)

2. For Last Quarter of the year:

Create a column as Quarter in backend using the following Master calendar scripts

MinMax:
LOAD
Min(DATE) as MinDate,
Max(DATE) as MaxDate
Resident Table;

Let vDateMin = Num(Peek('MinDate',0,'MinMax'));
Let vDateMax =Num(Peek('MaxDate',0,'MinMax'));

Master Calendar:

Load *,

'Q' & Ceil([Month (#)] / 3) as Quarter;

Load

Date($(vDateMin) + IterNo() - 1, '$(DateFormat)') as Date,

Month($(vDateMin) + IterNo() - 1) as Month,

Num(Month($(vDateMin) + IterNo() - 1), '00') as [Month (#)]

AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

DROP Table MinMax;

Then In Set Analysis:

Ex - sum({<Quarter={'Q4'}>}Sales)

Re: KPI for last quarter

alexanderdafflitto — Thu, 23 Jul 2020 08:41:51 GMT

Hello,

I would like to see the last quarter before the current. So for example, today I would like to see Q2 of 2020.

Alexander

Re: KPI for last quarter

Vegar — Thu, 23 Jul 2020 08:49:50 GMT

Asssuming your orignal expression is Sum(Amount) and you have the following calender dimensions: Year, Month, Quarter and Date. Then your set could look like this.

sum({< Year, Month, Quarter, Date= {">=$(=QuarterStart(today(),-1))<$(=QuarterEnd(today(),-1))"}>}Amount)

You can use this expression on this data model as an example:

SET DateFormat='YYYY-MM-DD'; Table: LOAD *, Dual('Q' & ceil(month(Date)/3)&'-'& year(Date),quartername(Date)) as Quarter , Year(Date) as Year , Month(Date) as Month Inline [ RowID, Date, Amount 001, 2019-01-01, 101 002, 2019-01-01, 102 003, 2019-06-16, 103 004, 2019-06-30, 104 005, 2019-02-01, 105 006, 2020-03-15, 106 007, 2020-04-10, 107 008, 2020-05-17, 108 009, 2020-07-01, 109 010, 2020-01-01, 110 011, 2020-01-01, 111 012, 2020-06-16, 112 013, 2020-06-30, 113 014, 2020-02-01, 114 015, 2019-12-24, 115 016, 2019-11-08, 116 017, 2019-10-02, 117 ];

Re: KPI for last quarter

alexanderdafflitto — Thu, 23 Jul 2020 11:53:40 GMT

Thanks. This seems to advance for my needs. Basically what I need is a way to format the dimension QuartersAgo so that I can see the actual quarter, not just -1 (for last quarter) or 0 for current quarter.

I now have a multi KPI with the last quarter as well as the current, but the labels are -1 and 0. Any way around this?

Alexander

Re: KPI for last quarter

Vegar — Thu, 23 Jul 2020 13:18:51 GMT

OK I think I understand.

Try something like this then:

'Q'&Ceil(month(QuarterName(today(),[QuartersAgo]))/3)&'-'& year(QuarterName(today(),[QuartersAgo]))

Re: KPI for last quarter

alexanderdafflitto — Thu, 23 Jul 2020 13:35:07 GMT

Where do I put that code?

Re: KPI for last quarter

Vegar — Thu, 23 Jul 2020 14:59:25 GMT

You could put it in the script where you have the Quartes ago field.

=IF(not isnull(QuartersAgo),'Q'&Ceil(month(QuarterName(today(),[QuartersAgo]))/3)&'-'& year(QuarterName(today(),[QuartersAgo]))) as [QuarterField]

Or you could create a custom field in your chart dimension using the definition

=IF(not isnull(QuartersAgo),'Q'&Ceil(month(QuarterName(today(),[QuartersAgo]))/3)&'-'& year(QuarterName(today(),[QuartersAgo])))

Notice I put a If(not isnull() ...) around it, realised that without it you will get a 'Q' where you don't have a QuarterAgo value.

Re: KPI for last quarter

alexanderdafflitto — Thu, 23 Jul 2020 17:34:30 GMT

I don’t seem to be able to use set analysis in the multi KPI chart?