I'm trying to compare the price between two dates within a date range. Here is an example of what I am attempting:My date range is Jan 2024 to June 2024Field 1: KPI, Table Column, Field shows the average price in Jan 2024Field 2: KPI, Table Column, Field shows the average price in June 2024I change the date range to March 2024 to May 2024Field 1: KPI, Table Column, Field shows the average price in March 2024Field 2: KPI, Table Column, Field shows the average price in May 2024Is there a way...

Date range comparative analysis | Help and How-To

Date range comparative analysis

I'm trying to compare the price between two dates within a date range.

Here is an example of what I am attempting:

My date range is Jan 2024 to June 2024
- Field 1: KPI, Table Column, Field shows the average price in Jan 2024
- Field 2: KPI, Table Column, Field shows the average price in June 2024
I change the date range to March 2024 to May 2024
- Field 1: KPI, Table Column, Field shows the average price in March 2024
- Field 2: KPI, Table Column, Field shows the average price in May 2024

Is there a way I can show this? I've tried a few different data functions within the widget function as well as filtered measure plugin.

One thing I was thinking, is it possible for the date range to auto-populate the filtered measures?

Such as:

I duplicate the date field in the Elasticube twice. Once is for Period1Date and the other Period2Date.
Period1Date would auto populate to be Jan 2024 and Period2Date would autopopulate to June 2024
Then I would use these two date fields in the widget expression

HQ_Dev_Prod

Posted 1 year ago·Last reply 1 year ago

7 comments

DRay

·1 year ago

Hi HQ_Dev_Prod,

I’m following up to see if the solution offered by AssafHanina worked for you.

If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

HQ_Dev_Prod

OP1 year ago

DRay and AssafHanina

Still testing it out. I was able to duplicate the date field in the elasticube for comparative analysis and was able to use it as a filter to compare two dates, but I wasn't able to use it in a manner to auto calculate the first period vs last period in a given date range.

Example:

- Range = Jan to June

- I can see average price for this full period

- But I want to see the price in Jan and then the price in June to compare.

Assaf Hanina

·1 year ago

Hey HQ_Dev_Prod ,

Based on the Use case described:
Range = Jan to June: does the range require?
Since the Compare is month (Yan) to month (June), the implementation using the filter measure:

Add 2 filters: Date and Date_compare (the duplicated column in the EC)
Set Date filter = Jan (01/2024 for example)
Set Date_Compare filter = June (06/2024 for example)
Create a new formula and add the Metric to Compare
Add a measure filter to the metric as the following example: (sum(total amount),@date)
add the same metric to the calculation (Assuming the calculation as the following: total amount(Jan) / total amount(June)) and add a measure filter for the metric (sum(total amount),@date_compare)
final output should be: (sum(total amount),@date) / (sum(total amount),@date_compare)

Best regards

HQ_Dev_Prod

OP1 year ago

AssafHanina thank you for the detailed step by step!
I was able to successfully build it this way. However, it requires the user to manually select @Date and select @Date_Compare.

Would it be possible for the start and end date be set automatically based on the selected date range?

Such as:

EC data contains date. Duplicate twice, one that is @StartDate and another that is @EndDate.
User selects date range to be January to June. (This selection does change dashboard, except for the widgets that contain @StartDate and @EndDate in the function.)
@StartDate auto selects to = Jan (Does not change dashboard data. Only when used in a function.)
@EndDate auto selects to = Jun (Does not change dashboard data. Only when used in a function.)

This way, in one dashboard I can see total values during this time period. With a comparative analysis between Start (Jan) and End (Jun).

Assaf Hanina

·1 year ago · Edited

hey HQ_Dev_Prod ,

thanks for sharing the full details.
From the understating, it's not a comparative dashboard where users can filter 2 custom range of Date filters. instead, users would like to filter on a Period meaning, Single date Filter.

Assuming using a Revenue as Metric, Users would like to see the KPI for Revenue for the entire period, and break by the Revenue for multiple Dimensions.

Additionally, users would like to be able to Review the Growth from the First Period (i.e. Jan) to the Last Period (i.e Jun) in respective to the Date filter. meaning if the range is different (Jan - March) the Growth calculation is Rev(March) / Rev(Jan)

For getting this, it's require to use a filter measure (not the plugin) to Get the Minimum and Maximum of Date according to the date Period.

Please find an example on behalf of the Sample Retail Datasource:

Open the Sample Retail datasource and add Custom Column: Month_year column to Dimdate table as the following calculation: getyear(date) * 100 + getmonth(date)
Import the dash file
Review the Dashboard Formula related for Min and Max:
It's using Top/Bottom 1 to filter the Max(Month_year)
Try to apply the same on your side

Dashboard Output:

Red Present Min Period
Green Present Max Period
Total Order Revenue relate to the entire period

Please note that it's a sample use case and may not work across multiple use case

Best regards

DRay

·1 year ago

Hello HQ_Dev_Prod,

I’m following up to see if the solution offered by AssafHanina worked for you.

Thank you.

Assaf Hanina

·1 year ago

hey HQ_Dev_Prod ,

the filter measure plugin allow you the option to slice the same KPI for two different date filters.
For example:

Field 1: KPI, Table Column, Field shows the average price in Jan 2024 - Filter on Date = 01/2024
Field 2: KPI, Table Column, Field shows the average price in June 2024 - Filter on Date_Compare = 06/2024

Please find the related post with an example of applying multiple date filters using the filtered measure plugin

Best regards