• John,

Have you tried using a Measured Value time filter within your sales calculation? I was able to achieve something similar by creating 3 functions - Previous Weeks Sales, Same Week Previous Years Sales and Growth %

Example:

Weekly Sales Total (Previous Week) would look something like this: (Sum([sales]),Weeks)

• Filter Weeks using Time Frame - Last Week

{
"last": {
"count": 1,
"offset": 1
}
}

Weekly Sales Same Week Previous Year would be the same, but a different filter applied to the Weeks field.

• Using the Time Frame option, click through to Advanced and input the amount of weeks prior you want to show.

{
"last": {
"count": 1,
"offset": 53
},
"custom": true
}

This function will look back and return the values 53 weeks from the current week. Allowing you to compare your previous weeks sales to the same week in the prior year's sales and can be used to calculate a Growth %.

Let me know if this works out.

Russell

• John,

This is frequently done in my company's KPI reports. We show all sales year over year and then calculate variances off of that all in the same pivot widget.

To do this in a Pivot Widget, we use the "Past Year" formula. See page 10 of this link: https://cdn.sisense.com/documentation/wp-content/uploads/2014/03/Formula-Editor-Quick-Ref-Guide1.pdf

I hope this helps.

-Ken

• Hi John,

Don't know if those to solutions have worked for you. I work with a client in retail, and none of these two solutions would work.

The classic PASTYEAR function don't work when you want to compare weeks as it is comparing days this year with the same date last year. So Monday 4/30/2018 to Sunday 5/6/2018 would be compared to the period from Sunday 4/30/2017 to Saturday 5/6/2017.

The measured value is definitely a nice solutions, but it is not always working as most years do have 52 weeks, but some years have 53.

The solution I use in many occasions is to use an external calendar table, defined through an Excel spreadsheet. In this calendar I define several columns, the most important being for each date the week number to use by default for YoY comparisons (that corrects week number when a year has 53 weeks).

Then in the dashboards I select the year and the week to study. For each KPI I can then now use PASTYEAR and it works as expected. The advantage of this solution is that you can work with different period of times, as long as you define them in the custom calendar table. When you select week or weeks, you can also drill down to days to see more details.

This method also lets you define some group of weeks to define month equivalent. The most common use case would be to work with NRF calendar.

In order to automate the dashboards, you just need to add some custom columns to calculate dynamically some selectors for "current week" and "past week", plus any standard time frame necessary.

If you need more explanation, don't hesitate to ask.

David.

• I am working to create a benchmark in the Elasticube that takes a Unique Count of a value from the same day on the previous year and increases it by 3%.

SELECT

COUNT(m.[value]) AS [Volume]
, m.[Date] AS [Date]

--Temp for Testing
,(ToInt(COUNT(m.[value]) * 1.03)) AS [BenchmarkTestValue]

--CASE Statement for Last Year Benchmark Percentage (increase 3%) ****STILL NOT WORKING****
,CASE WHEN m.[Date]= CreateDate((ToInt(GetYear(m.[Date])-1)),GetMonth(m.[Date]),GetDay(m.[Date]))
THEN (ToInt(COUNT(m.[value]) * 1.03))
ELSE Null END AS [Benchmark Target]

FROM [Main] m
WHERE m.[Date] <= Now()
AND m.[Date] >= CreateDate((ToInt(GetYear(Now())-1)),GetMonth(Now()),GetDay(Now()))
GROUP BY m.[Date]

• Hello,

On this same subject of weekly calculations, is there any way to create a dashboard with weekly data (Sun through Sat) for the past year or so working with daily totals?

In other words, I have data by date but I would like to see one row of data for the last week, another for the week before that, and so on going back for several months.  So I want seven days of daily data to be aggregated into one number for the week.

I am not a Java script expert and I saw the syntax referenced in a prior message but could I do this for dozens of historical weeks?  If so, could you please help with the methodology?

Thank you for any assistance.

Cushrow

• Hi Cushrow,

For what you are looking for, you should not need any formula, SQL or Javascript. Just use the date as week in your axis. For instance, in a bar chart you get the attached chart :

In the chart, each bar is for a week. You can format the week number by using the small icon next to the toggle, visible when you hover the mouse.

Hope this helps.

Best,

David.