Filtered date selection

Kanokkarn · August 5

Hi there

Im trying to create two filter date selection to see sale difference between two period selection.

Is there any way to do this?

Sean_Tully · August 5

You could do this using variables and beast modes. You'd need four variables (period 1 start, period 1 end, period 2 start, period 2 end). Then you can create two beast mode to use those those variables to get your sales (eg, period 1 sales could be sum(case when date_field >= period_1_start and date_field < = period_2_start then sales end).

If you need to calculate the difference, you could make a third beast mode subtracting one of your sales beast modes from the other.

ArborRose · August 5

There's several ways we could interpret the question. I think you are looking for something where your periods are static (firm, typed, defined). In this case my solution wouldn't incorporate "filters" as a drop down.

Let's say we have a sample set:

Date,Product,Amount

2024-01-01,Product A,10000.00

2024-01-05,Product B,15000.00

2024-01-10,Product A,12000.00

2024-01-15,Product C,18000.00

2024-01-20,Product B,13000.00

2024-01-25,Product A,11000.00

2024-02-01,Product C,20000.00

2024-02-05,Product B,16000.00

2024-02-10,Product A,14000.00

2024-02-15,Product C,22000.00

And define Period 1 calculated field as

SUM(CASE 

  WHEN `Date` >= '2024-01-01' AND `Date` <= '2024-01-31' 

  THEN `Amount` 

  ELSE 0 

END)

And another calculated field or Period 2 as

SUM(CASE 

  WHEN `Date` >= '2024-02-01' AND `Date` <= '2024-02-29' 

  THEN `Amount` 

  ELSE 0 

END)

And we create Difference as a calculated field:

SUM(CASE 

  WHEN `Date` >= '2024-02-01' AND `Date` <= '2024-02-29' 

  THEN `Amount` 

  ELSE 0 

END) - 

SUM(CASE 

  WHEN `Date` >= '2024-01-01' AND `Date` <= '2024-01-31' 

  THEN `Amount` 

  ELSE 0 

END)

And Percentage Change as:

(SUM(CASE 

  WHEN `Date` >= '2024-02-01' AND `Date` <= '2024-02-29' 

  THEN `Amount` 

  ELSE 0 

END) - 

SUM(CASE 

  WHEN `Date` >= '2024-01-01' AND `Date` <= '2024-01-31' 

  THEN `Amount` 

  ELSE 0 

END)) / 

SUM(CASE 

  WHEN `Date` >= '2024-01-01' AND `Date` <= '2024-01-31' 

  THEN `Amount` 

  ELSE 0 

END) * 100

The card may look like this:

ArborRose · August 5

I believe what @Sean_Tully is expressing is - to filter on something changing, the dates within an example like mine, would need to be a variable. You could take my example farther in defining period1_start_date, period1_end_date, period2_start_date, and period2_end_date. And then use them in the calculations.

Kanokkarn · August 6

@ArborRose hi thank you, yes i would need the date to be filtered by user between period 1 and period 2. I dont want to define the range between Period1 and 2 since the user will be the one who filtered.

@Sean_Tully @ArborRose could you guys please show me how to do this since im so junior on DOMO

Kanokkarn · August 6

i have creating this but want those variable to be filtered in dashboard. can you guys show me?

ArborRose · August 6

You have defined four variables: P1_start, P1_end, P2_start, and P2_end. It appears that you have also made those into controls.

Next, change the calculated fields so they make use of the variable instead of static values. Then, when the variables are changed on the controls panel, the values on the card will change accordingly.

Kanokkarn · August 7

@ArborRose

Hi since i have many value to show in the table and want period to show as a row in picture below

but I see that we need to set their own period ie. period_1_cost, period_2_cost, period_1_clicks, period_2_clicks

is there a way to create a table like this?

ArborRose · August 8

I don't have time to replicate it as an example today. I think you need to create custom columns and rows to get the comparisons over each metric.

Replace Amount with the relevant column for Order. Replace Amount with the relevant column for Unit Sold.

Overall Period 1 & 2…

GMV_P1:

SUM(CASE 

  WHEN `Date` >= 'P1_start' AND `Date` <= 'P1_end' 

  THEN `Amount` 

  ELSE 0 

END)

GMV_P2:

SUM(CASE 

  WHEN `Date` >= 'P2_start' AND `Date` <= 'P2_end' 

  THEN `Amount` 

  ELSE 0 

END)

GMV_Percent_Change:

(SUM(CASE 

  WHEN `Date` >= 'P2_start' AND `Date` <= 'P2_end' 

  THEN `Amount` 

  ELSE 0 

END) - 

SUM(CASE 

  WHEN `Date` >= 'P1_start' AND `Date` <= 'P1_end' 

  THEN `Amount` 

  ELSE 0 

END)) / 

SUM(CASE 

  WHEN `Date` >= 'P1_start' AND `Date` <= 'P1_end' 

  THEN `Amount` 

  ELSE 0 

END) * 100

For Laz, SHP, TT, you'll need to filter the data to include only those specific platforms.

GMV_P1_Laz:

SUM(CASE 

  WHEN `Platform` = 'Laz' AND `Date` >= 'P1_start' AND `Date` <= 'P1_end' 

  THEN `Amount` 

  ELSE 0 

END)

GMV_P2_Laz:

SUM(CASE 

  WHEN `Platform` = 'Laz' AND `Date` >= 'P2_start' AND `Date` <= 'P2_end' 

  THEN `Amount` 

  ELSE 0 

END)

GMV_Percent_Change_Laz:

(SUM(CASE 

  WHEN `Platform` = 'Laz' AND `Date` >= 'P2_start' AND `Date` <= 'P2_end' 

  THEN `Amount` 

  ELSE 0 

END) - 

SUM(CASE 

  WHEN `Platform` = 'Laz' AND `Date` >= 'P1_start' AND `Date` <= 'P1_end' 

  THEN `Amount` 

  ELSE 0 

END)) / 

SUM(CASE 

  WHEN `Platform` = 'Laz' AND `Date` >= 'P1_start' AND `Date` <= 'P1_end' 

  THEN `Amount` 

  ELSE 0 

END) * 100

Repeat calculations for SHP, TT by replacing Laz with SHP and TT.

You columns would have

GMV (P1, P2, Percent Change)
Order (P1, P2, Percent Change)
Unit Sold (P1, P2, Percent Change)

And your rows would have

Overall
Laz
SHP
TT

Filtered date selection

Answers

Categories