# Filtered date selection

Member

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?

## Answers

• Contributor

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.

• Coach

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:

** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **

• Coach

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.

** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **

• Member

@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

• Member

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

• Coach

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.

** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **

• Member

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?

• Coach

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

** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **