Compare current week, same week in last month and same week in last year

Question & Answer
, , , , ,
1

Hi, I am new to quick sight and i have below problem to solve, could you please help.
I have an order date and order count columns, i want to plot this to a vertical bar chart. And in each bar i want to show the aggregation of order counts in current week, same week in last month and same week in last year (up to last 7 weeks) (This is to help the customers to compare the data based on week)
Could you please help me to achieve this.

2

Hi @Ren
maybe you can have a look at:

and

BR

1 Like
3

@ErikG thanks for the reply. I already tried the method in first link
It works for me in case of day, I can plot current day, same day in last week and same day in last month
using below formulas
PeriodOverPeriodLastValue(sum({order_count}), {Date (Period)}, WEEK, 1)
PeriodOverPeriodLastValue(sum({order_count}), {Date (Period)}, MONTH, 1)

But couldn’t understand how we can use the same method in case of WEEK,
I want the current week, same week in last month and same week in last year

depends on the time span (as parameter) selected (DAY, MONTH, etc…) this calculated filed changes - Date (Period
ifelse(
${SelectedTimeSpan}=‘DAY’,truncDate(“DD”,{order_date}),
${SelectedTimeSpan}=‘WEEK’,truncDate(“WK”,{order_date}),
${SelectedTimeSpan}=‘MONTH’,truncDate(“MM”,{order_date}),
truncDate(“YYYY”,{order_date}))

I couldn’t achieve what i wanted by changing the granularity and offset in period function in case of WEEK.

4

Any help on the above issue is very much appreciable, since I am stuck here.

5

@Ren -
This should get you same week last year:
periodOverPeriodLastValue(sum(Sales), {Order Date},WEEK, 52)

Not sure how you want to calculate same week last month, but if you want to go 4 weeks back you could do this:
periodOverPeriodLastValue(sum(Sales), {Order Date},WEEK, 4)

1 Like
6

@Kellie_Burton thanks for the reply
I could do the year case as you mentioned.
But in case of week, for example if it is 2nd week of current month I want to compare it with last month 2nd week. If there is no such week number in last month, get the data of last week.

7

HI @Ren
would you like to compare the second week of the last month or the week of the last month of the date?
e.g.

week 11 = is March 11
would you compare with the week of Feb 11 or week 7?

BR

1 Like
8

Sorry for the late reply
Yes Erik, we want to use the week of Feb 11 for comparison (week 7 in 2024)

9

Hello @Ren, @ErikG , and @Kellie_Burton !

@Ren are you still working on this problem or were you able to find a solution? If you were able to find a solution could you share it to help the community!

In the past when I have had trouble using the periodoverPeriod functions I have used conditional statements using addDateTime. So I might do something like this:

ifelse(
{order date} <= addDateTime(-52, 'WK', now()) AND
{order date} >= addDateTime(-51, 'WK', now()),
{sales},
NULL)

My example is a bit exaggerated, but what you can sort of use addDateTime to create a window for the data to be pulled. You can change now() into whatever date time field you have, or use a parameter to make it dynamic.

10

Hi @Ren,
It’s been awhile since we last heard from you. Were you able to work out a solution for your original request or are you still having issues? If you were able to solve, please share with the community what worked for you.

If you are still in need of assistance, let us know and we can try to help. If we do not hear back within the next 3 business days, I’ll go ahead and archive this topic. Past that, feel free to create a new topic for discussion regarding any outstanding requests.

Thank you!

11

We were working on this issue as part of a feasibility to use quick sight in our web application. Anyway without solving this issue completely, we decided to use it, so once the development starts we may start to work on this issue again. But currently we can close this issue. Thanks for your help

1 Like