Beast Mode POP Day Calculation

nlonkai · October 2017

Hi everyone!

I am currently trying to create 2 variance calculations and 3 YTD calculation.

Lets start with the 2 variance calculations:

The first variance should calculate the Total Sales of each day and reduce it with the Total Sales of a day in the prior year.
The second variance should do the same, but in %

The YTD calculation should sum up Total Sales per day. Afterwards I want to create additional fields where I calculate two variances like above.

I have an issue with it, because our fiscal year starts at the first of May and ends at the end of April and secondly my variances should not just compare my figures with last year, we need to show variances based on weekdays this year vs. last year. I tried to illustrate it in the enclosed excel.

I tried to figure it out on my own by using other DOJO entries and DOMO University, but all suggestions led to errors or wrong calculations.

I would really appreciate help from you guys!

Many thanks

Nikolaus

RGranada · November 2017

So... Been reviewing this as best as I can and here some modifications to the code:

Where Proc1:

Select a.Dates
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)+ (1000*d.a)) DAY as Dates
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Dates between '2005-01-01' and '2025-01-01'

Proc_Group:

Select created_at_trans,platform, Sum(Total) AS Total From tst_dojo_dod GROUP BY created_at_trans,platform

Proc_join:

SELECT dt.dates as datum, d.Platform,d.Total,DATE_SUB(d.created_at_trans, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(d.created_at_trans)-1),'%m-%d-%Y'),0) week) AS LY_Date

From proc1 dt LEFT JOIN Proc_Group d ON dt.dates = d.created_at_trans

Proc2:

Select n.*,n1.total AS Ly_Total
From proc_join n LEFT JOIN tst_dojo_dod n1 on n1.created_at_trans = n.ly_date
AND n1.Platform=n.Platform

Output Dataset:

SELECT Datum,Platform,IFNULL(Total,0) as Total ,IFNULL(Ly_Total,0) AS Ly_Total, Ly_Date FROM proc2

The order is the same as the last post.

Sending you also the attachment with the results, from the latest data you sent.

If you get null values they should be from days with no sales in the current year. Proc_group is intended to avoid days with multiple rows.

Tell me how it goes.

RGranada · October 2017

Hi,

Let see if we can tackle thin one thing at a time.

This beast mode should give you the variance between a weekday and the same weekday last year:

SUM(`Sales`)
-
SUM(CASE WHEN `Date`= DATE_SUB(`Date/Time Opened`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Date`)-1),'%m-%d-%Y'),22) week) THEN `Sales` END)
/
SUM(CASE WHEN `Date` = DATE_SUB(`Date`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Date`)-1),'%m-%d-%Y'),22) week) THEN `Date` END)

This is accounting for ISO calendar year where Monday is the start of the week.

Try this out and let me know how it went.

Regards

nlonkai · October 2017

Hi,

I tried it:

SUM(`Total`)
-
SUM(CASE WHEN `Datum`= DATE_SUB(`Date/Time Opened`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Total` END)
/
SUM(CASE WHEN `Datum` = DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Datum` END)

What do you mean with the field: `Date/Time Opened`

RGranada · October 2017

Hi,

My error, it's just your date field...

Here is it corrected:

SUM(`Total`)
-
SUM(CASE WHEN `Datum`= DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Total` END)
/
SUM(CASE WHEN `Datum` = DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Datum` END)

So... Did it work?

Regards,

nlonkai · October 2017

Hi,

now I get an Invalid Formula : This calculation contained an error.

SUM(`Total`)
-
SUM(CASE WHEN `Datum`= DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Total` END)
/
SUM(CASE WHEN `Datum` = DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Datum` END)

RGranada · October 2017

Ok, there was another error....

try:

SUM(`Total`)
-
SUM(CASE WHEN `Datum`= DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Total` END)
/
SUM(CASE WHEN `Datum`= DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Total` END)

nlonkai · October 2017

Hi, unfortuanetly it doesn´t work again.

I am not really sure what everything in the formula means, so would you like to see my data source in more detail (without actual sales of course)?

Thanks

Nikolaus

RGranada · October 2017

OK, good idea...

Send me a sample.

nlonkai · October 2017

I change my first upload a bit.

RGranada · October 2017

Is that what your dataset looks like?

This is helpful but I would need a sample of the real DOMO dataset.

Can you provide it?

nlonkai · October 2017

I tried to make an example.

It is a bit difficult, because I use about 5 different sources directly linked to our SQL server.

The example only shows the Total sales, in reality we have about 20 different sales figures, but I think that wouldn´t be necessary for the example.

Furthermore, I grouped everything by Productgroup and Platform in my card.

I hope that the example helps.

Thanks

RGranada · October 2017

Hi,

Upon deeper analysis, I think using a Dataflow will be simple and perhaps the only way to achieve your goal.

Here is the MySQL Code you can use in A MySql Dataflow:

Select n.*,n1.total AS Ly_Total,DATE_SUB(n.Datum, interval WEEKOFYEAR(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y')) week) AS Ly_Date

From your_dataset_name n LEFT JOIN your_dataset_name n1 on n1.datum=DATE_SUB(n.Datum, interval WEEKOFYEAR(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y')) week)

Basically, this will give you all the columns of your dataset plus the date of the same weekday last year(Ly_Date), and the total sales of the same weekday last year(Ly_Total).

Then you can use this Beast Mode to calculate the % Sales Variance between the day current year and the same weekday last year:

(SUM(`Total`)
-
SUM(`Ly_Total`))
/
NULLIF(SUM(`Ly_Total`),0)

Here is an example Card in the analyzer:

Note that in this case the date filter of the card is set to >= 01/01/2017 since you don't have values from 2015 in the sample(if you don't set the filter it will give null values from the variance in 2016 dates, there are other ways to approach this).

I'm sending an attachment with the exported results too.

See if it works for you and we will progress from here.

nlonkai · October 2017

Hi,

I checked you approach and it nearly worked out. I had 2 issues:

1) For my first try I used a dataset with over 300k rows. I received a message which was something like:"Ups something went wrong".

Afterwards I used a smaller one, which worked (about 6,5k rows). I guess that I received a time out or something

2) As you mentioned the data in my sample is only 2017. In reality my figures go back to 2013. Unfortunately, I received a different result for years before 2017 for example:

In DOMO: 12/22/2016 vs. 12/17/2015

Correct dates: 12/22/2016 vs. 12/24/2015

For 2016 he goes back to the 17th, although he should go the 24th.

I think the problem perhaps is that it is not a continuous logic. Would it be possible to submit an excel with a matching of dates? Afterwards I could use your join to attach the correct sales figure to it.

What do you think?

Thanks by the way for your afford!!

RGranada · October 2017

Hi,

In fact, it should be a continuous logic, it all depends on how you define your year in terms of Week numbers.

Try the to change the MySql query to this :

Select n.*,n1.total AS Ly_Total,DATE_SUB(n.Datum, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y'),0) week) AS Ly_Date
From tst_dojo_dod n LEFT JOIN tst_dojo_dod n1 on n1.datum=DATE_SUB(n.Datum, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y'),0) week)

This should address your problem (check other dates too...) the difference is that this one uses the WEEK(date,[Mode]) function where you can define how you count your weeks of the year. You can change the number in bold on the query (Mode) according to next table and see what better defines your year.

The following table describes how the mode arguments work:

Mode First day of week Range Week 1 is the first week …

0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with more than 3 days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with more than 3 days this year
4	Sunday	0-53	with more than 3 days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with more than 3 days this year
7	Monday	1-53	with a Monday in this year

Your idea of joining to a pre-made table containing the last year dates is valid, but in my view adds a layer of not needed governance to maintain that table...

Sending the result excel for you to check.

Let me know how it goes...

Regards.

nlonkai · October 2017

Hi,

I had to add a previous step before applying your code. I had to create a grouping on my "Datum", so I don´t have more than one value per day, which caused an error within your code.

If there is more than one sale per day your join will take the first sale of the day and multiply it by the quantity of sales for that day.

Now I have a different problem:

If I have a sale in Total LY, but I don´t have a sale on the related day in the next year. The amount in the last year disappears.

eg:

Sale:

11/19/2014 - $100 vs. 11/20/2013 - $ 90

nex year

11/17/2015 - $110 vs 11/18/2014

11/20/2015 - $120 vs 11/21/2014

So basically, the calculation doesn´t include last year values, if there is not a sale in the next year.

RGranada · October 2017

Hi,

You are correct in the grouping, my bad!

As for the missing Dates, you will have to complete the missing intervals in your original data set.

To accomplish that you can use the following Query that returns all the dates in a specific interval and join it to your table in a transform prior to the other query. This will make sure that even if the current year doesn't have sales it will join with that date in the last year and get it's values if they exist.

Here is the query:

Select a.Dates
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Dates
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a where a.Dates between '2005-01-01' and '2025-01-01'

Let me know how it goes.

Regards,

RGranada · October 2017

Hi,

You will have to complete the missing date intervals in your dataset. You can do this by joining the original data set with the following one that provides all the dates in an interval. This the query will take into account all days in the current year disregarding having or not sales.

This is the Query:

Select a.Dates
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Dates
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Dates between '2005-01-01' and '2025-01-01'

This is how I arranged it in my test data flow:

Where Proc1:

Select a.Dates
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Dates
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Dates between '2005-01-01' and '2025-01-01'

Proc_join:

SELECT dt.dates as Datum, d.Plattform,d.Productgroup,d.Total From tst_dojo_dod d LEFT JOIN proc1 dt ON d.Datum=dt.dates

Proc2:

Select n.*,n1.total AS Ly_Total,DATE_SUB(n.Datum, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y'),0) week) AS Ly_Date
From proc_join n LEFT JOIN tst_dojo_dod n1 on n1.datum=DATE_SUB(n.Datum, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y'),0) week)

Output Dataset:

SELECT Datum,Plattform,Productgroup,IFNULL(Total,0),Ly_Total,Ly_Date FROM proc2

Tell me how it goes.

Regards,

nlonkai · October 2017

Hi,

I tried your sql code, but I included a grouping after your select a.Dates code.

Enclosed you will find the result inserted into an excel.

Basically, I have a lot of values without any linked date.

Maybe I have to change something in the formula.

Thanks

Nikolaus

RGranada · October 2017

Hi,

I reviewed some of the code and came to the conclusion that I was only joining dates when you have multiple sales per date (ProductGroup, Platform).

Made changes to "Proc2" transform :

**Select n.*,n1.total AS Ly_Total,DATE_SUB(n.Datum, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y'),0) week) AS Ly_Date
From proc_join n LEFT JOIN tst_dojo_dod n1 on n1.datum=DATE_SUB(n.Datum, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y'),0) week)
AND n1.Plattform=n.Plattform AND n1.Productgroup=n.Productgroup**

Now, this joins current year sales with their last year equivalent in terms of Date, Platform, and ProductGroup.

You may have to review your grouping, as this could be the fact that was generating duplicate sales.

Give this a try.

By the way, what was the grouping you included?

Regards

nlonkai · October 2017

Hi,

I tried your code, but unfortunately I have still the same result in terms of values without dates.

I deleted Productgroups, because it was an additional field I created in my source. So that field is not necessary at this stage (I will include it on a later point within the ETL Flow).

Regarding my grouping.

I grouped all necessary fields (eg. Total) based on created_at_trans (which I will name "Datum" within ETL Flow).

RGranada · October 2017

I do not have that problem....

Maybe my data sample is not accurate...

If you can send me the exact data you are using before the joins, I will try and give it a look.

Regards

nlonkai · October 2017

Hi, so I tried to create an example. Unfortunatley, I can´t give you real figures.

The difference between my example source and the real source is that the real source is connected to our SQL server, it includes many more turnover columns and more IDs and Dates. But my example should illustrate which data we want to integrate into this report.

Thanks for your help!

tiffanywijaya · October 2017

Hi Ricardo, this solution works for me, thank you! But I don't understand the part where we subtract 22 weeks from the current date- can you explain why? I was thinking of doing subdate(current_date(),interval 52 week) instead- it seems to give me the same results. Thanks in advance

RGranada · October 2017

Hi @tiffanywijaya,

That is a part of the formula where I calculate the number of weeks last year had, in order to subtract that number of weeks (Some years can have 53 weeks) :

WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Date`)-1),'%m-%d-%Y'),22)

I get the week number for the last day of the year and use that for the subtraction.

The "22" you are referring sets the weekofyear function to count weeks following the ISO calendar where Monday is the start of the week, you could also use "11" where Week starts January 1st and Sunday is the start of the week.

Here' a great link on that:

http://knowledge.domo.com?cid=beastmodereference

Regards,

nlonkai · October 2017

Hi @RGranada!

Did you have the chance to take a look at my data source example?

I am just wondering if this will work.

Many thanks!

Nikolaus

[Deleted User] · October 2017

@nlonkai,

Be sure to make the relevant post from @RGranada as the solution once this is finalized.

Thank you @RGranada for the great contribution here and to everyone for the collaboration!

Regards,

RGranada · November 2017

So... Been reviewing this as best as I can and here some modifications to the code:

Where Proc1:

Select a.Dates
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)+ (1000*d.a)) DAY as Dates
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Dates between '2005-01-01' and '2025-01-01'

Proc_Group:

Select created_at_trans,platform, Sum(Total) AS Total From tst_dojo_dod GROUP BY created_at_trans,platform

Proc_join:

SELECT dt.dates as datum, d.Platform,d.Total,DATE_SUB(d.created_at_trans, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(d.created_at_trans)-1),'%m-%d-%Y'),0) week) AS LY_Date

From proc1 dt LEFT JOIN Proc_Group d ON dt.dates = d.created_at_trans

Proc2:

Select n.*,n1.total AS Ly_Total
From proc_join n LEFT JOIN tst_dojo_dod n1 on n1.created_at_trans = n.ly_date
AND n1.Platform=n.Platform

Output Dataset:

SELECT Datum,Platform,IFNULL(Total,0) as Total ,IFNULL(Ly_Total,0) AS Ly_Total, Ly_Date FROM proc2

The order is the same as the last post.

Sending you also the attachment with the results, from the latest data you sent.

If you get null values they should be from days with no sales in the current year. Proc_group is intended to avoid days with multiple rows.

Tell me how it goes.

nlonkai · November 2017

Many thanks for your help!

I had to tweak a thing or two, but it worked out!

Again, I appreciated your effort!

Beast Mode POP Day Calculation

Best Answer

Answers

Categories