topic Re: Variation (%) between Month for Current Year and The same Month of the last year in App Development

Variation (%) between Month for Current Year and The same Month of the last year

FARQLIK — Tue, 07 Mar 2023 17:15:32 GMT

Hello Community,

I want to calculate the variation (%) of the amount beetwen the current Month and the same Month of the previous Year .

Does any one Have a solution ?

Thank You for your support and help

Best regards

Re: Variation (%) between Month for Current Year and The same Month of the last year

rpennacchi — Tue, 07 Mar 2023 18:40:15 GMT

You can create a dim_Calendar like these:

TRACE Creating Calendary;

let vMin_Date = '01/01/2022';
let vMax_Date = TODAY();

// Generate a record of each day betwen two dates

[TMP Dim_Calendary]:
LOAD
'$(vMin_Date)' + ROWNO()-1 as DATE_TEMP
AUTOGENERATE vMax_Date-vMin_Date+1;

Dim_Calendary:
NOCONCATENATE LOAD
DATE_TEMP+0 as ORDER_DATE_KEY, // KEY FIELD !!!
DATE(DATE_TEMP) as ORDER_DATE,
-InYearToDate(DATE_TEMP, '$(vMax_Date)', 0) as DATE_IS_IN_ACTUAL_YEAR, // Actual Year
-InYearToDate(DATE_TEMP, '$(vMax_Date)', -1) as DATE_IS_IN_LAST_YEAR // Last Year
RESIDENT [TMP Dim_Calendary];

DROP Table [TMP Dim_Calendary];

Remember that your date field from your fact table needs to have the same name than ORDER_DATE_KEY.

Now on your dashboard you can create a set analysis on your KPI like:

(Sum({DATE_IS_IN_ACTUAL_YEAR = {1}}SALES) - Sum({DATE_IS_IN_LAST_YEAR = {1}}SALES)) / Sum({DATE_IS_IN_LAST_YEAR = {1}}SALES)

Re: Variation (%) between Month for Current Year and The same Month of the last year

rpennacchi — Tue, 07 Mar 2023 18:50:04 GMT

In case you want to calculate period over period, I suggest you to learn more about as-of tables.

This post explains all you need to know to create more flexible scenarios.

The as-of Table