Comparison of values for different time periods (EX. week 1 june 2023 compared to week 1 june 2024)

Question & Answer
, , ,
1

I want to create visuals using a calculated field that compares period over period values. For example, I a bar graph or line graph that will show values for week 1 of june 2023 compared to week 1 of june 2024. I want to compare the same dates (week and month) for different years. how would I achieve that?

2

Hi @huda.h.yazgi ,

Are you looking for single metric as output e.g. delta = (this year metric - last year metric ) or line chart with multi line for each year but at week month metrics granular level?

Thanks,
Prantika

3

image
this is an example of what I am trying to do. I would probably use it in an insight to say something like “the number of cases in week 1 june 2023 where higher (987) than the number of cases in week 1 of june 2022.”
or maybe I can have a line graph with 2 lines - one for the weeks in june 2022 and one for 2023.
hope that made sense

4

Got it. I would solve this by getting Week of month field and using lag to get previous year metrics.
Let me try to add step by step execution that I can think ok.

  1. Step 1: Create a Week of month calculated field to get week 4, july 2023
    Below is a sample formula giving July-Wk 1
    concat(switch(extract(‘MM’, orderdate),

1,‘January’,

2,‘February’,

3,‘March’,

4,‘April’,

5,‘May’,

6,‘June’,

7,‘July’,

8,‘August’,

9,‘September’,

10,‘October’,

11,‘November’,

12,‘December’,

‘NA’),‘- Wk’,toString(dateDiff(truncDate(‘MM’,orderdate),truncDate(‘WK’,orderdate),‘WK’)+1))

  1. Create a calculated field to return the Year value of the date
  2. Create a calculated field to find the previous year value.
    Sample formula looks like :
    lag(sum({Bill amount}),[{Week month} ASC, year ASC],1,[{Week month}])
  3. Calculate Change% from the above fields.

Please note this works well with tabular representation, since we are using window function lag. For custom narrative, please add references fields or tweak the formula as required.

Hope this helps!

5

Thank you so much for your reply. I already have the week field in my dataset. what I am trying to achieve is a calculation that compares the number of cases in a week to the number of cases in the same week but the previous year. i.e compare the number of cases from week 1 june 2023 to week 1 june 2024. how would I achieve that?

6

Hi @huda.h.yazgi ,

If you already have the weak number and year as fields, then you can try from step 3 and 4.
Step 3 gives you the calculation for last year same week month. And once you deduct the current and previous year you would get the required.

Sample solution which I tried out. You can check the formula here.

Thanks,
Prantika

7

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!