Getting period-specific calculations on KPI based on date period

Question & Answer
, , , , ,
1

Hi everyone, I’m trying to build KPI’s which show the current Revenue and GMV. I have the Revenue/GMV field as my value, and the date period as my trend group. The date is aggregated by month as of now. Here’s a sample KPI of what it looks like:
image
However, the issue here is that the KPI compares this month to the previous month, despite this month not being over yet. Is it possible to something where it does an equivalent comparison? So March data (which is until today, 26/3/24) is compared to 1st Feb to 26th Feb. Basically comparing to the same period. Is that possible? If so, how do I get around to doing it?

2

Hi
You can try this to achieve your desired output.

ifelse(
// condition for 30 days (month)
({extract_month_select_date} = 11 OR {extract_month_select_date} = 9 OR {extract_month_select_date} = 6 OR {extract_month_select_date} = 4)
AND {datediff_selectdate_month} =29 ,
// then
ifelse(
{date} >= truncDate(‘MM’,addDateTime( -1, ‘MM’,${selectdate})) AND {date} <= addDateTime( 2,‘DD’,addDateTime(-1, ‘MM’,${selectdate}))
,{Revenue},0) ,

// condition for February
{extract_month_select_date} = 2 AND {datediff_selectdate_month} =27 ,

// then
ifelse(
{date} >= truncDate(‘MM’,addDateTime( -1, ‘MM’,${selectdate})) AND {date} <= addDateTime( 4,‘DD’,addDateTime(-1, ‘MM’,${selectdate}))
,{Revenue},0) ,

// condition for February leap year
{extract_month_select_date} = 2 AND {datediff_selectdate_month} =28 ,

// Then
ifelse(
{date} >= truncDate(‘MM’,addDateTime( -1, ‘MM’,${selectdate})) AND {date} <= addDateTime( 3,‘DD’,addDateTime(-1, ‘MM’,${selectdate}))
,{Revenue},0) ,

// Else ( if select date is not last day of month)
ifelse(
{date} >= truncDate(‘MM’,addDateTime( -1, ‘MM’,${selectdate})) AND {date_date} <= addDateTime( 1,‘DD’,addDateTime(-1, ‘MM’,${selectdate}))
,{Revenue},0)

)

1 Like
3

I’ll try this out and update you shortly. Could you guide me to understanding the logic behind your calculation?

4

We are using select date parameter so if you select 26 March, it will take data from 1st march till 26 march and also take data from same date but previous month like 1 to 26 Feb in the separate field. so, after that we are using target value. ( basically you need two separate calculated fields one for this month and other for previous month)

1 Like
5

So the way I’m filtering out my dates is by using the Order Date field in my table. I’m using that as a filter rather than creating a parameter. Is that similar to what you’ve done?

6

We can’t use filter in calculated filed for that you need to use parameter to compare both dates.

7

@Shahid_Muhammad So to do this I’ll need a start date and end date parameter?

8

Yes, either you can use start end date parameter or select date parameter as per your requirement.