KPI for last quarter

[alexanderdafflitto]

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?

[Vegar]

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

[alexanderdafflitto]

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. 

[sugisense]

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)

[alexanderdafflitto]

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

[Vegar]

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
];

[alexanderdafflitto]

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

[Vegar]

OK I think I understand. 

Try something like this then:

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

[alexanderdafflitto]

Where do I put that code?

[Vegar]

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.