Reply
Highlighted
Purple Belt
Posts: 191
Registered: ‎04-09-2015
Accepted Solution

Convert date to show # of weeks a product is on sale

I'm looking to create a line graph that shows the sales over time for two product lines.

 

These products launched at different points in time, let's say Product A launched Jan of 2016, and Product B launched Aug of 2016.

 

I have their sales saved for each week, ex week of 1/3/16, week of 1/10/16 (Week Of column is date format).


I would like to write a calculation that takes the earliest week found for each product and labeling it Week 1, and advancing each week from there.

 

So product A that launched the week of 1/3/16, which is that product's Week 1, would graph at the same point on the X axis as product B's Week 1, week of 8/7/16. 

 

X axis would show week 1, 2, 3, 4, and for Product A that would correlate to sales from week of 1/3/16, 1/10/16, 1/17/16, 1/24/16, and Product B would correlate to week of 8/7/16, 8/14/16, 8/21/16, 8/28/16.

 

Anyone want to help?

 

 

Broadway + Data

Accepted Solutions
Solution
Accepted by topic author RobynLinden
‎02-08-2017 03:16 PM
Purple Belt
Posts: 119
Registered: ‎04-09-2015

Re: Convert date to show # of weeks a product is on sale

Domoslide1.JPG

 

@RobynLinden hope all is well! I have a solution - takes a few steps but works - depending on what else you are doing this will also allow new products and start dates to be added just by having them show up in the dataset.  The picture above is the output.

 

I used two Magic ETLs to do this.  First one, bring in the data set (Product Test in pics below).  group by Product and add new Column 'Start Date' which is Min(`Sale Date`)

 

Domoslide2.JPG

 

Domoslide3.JPG

 

This creates an output file (product Test 2) that I use in the second Magic ETL 

In the second one bring in original data set and the new data set and link on Product.

Select columns will allow you to only ring one of the product columns and now you have the Sale Date and Start Date in each product line.

 

Domoslide4.JPG

 

Domoslide5.JPG

 

Domoslide6.JPG

 

Use this data set to create the card.  Open beast mode and it is a simple beast mode:

 

Domoslide7.JPG

 

Now you can create the simple card

 

Domoslide8.JPG

 

This works - may be a more elegant way but I hope this helps!

 

@Shevy

Dojo Community Member
** Please like responses by clicking on the thumbs up
** Please Accept / check the answer that solved your problem / answered your question.

View solution in original post


All Replies
Green Belt
Posts: 70
Registered: ‎05-22-2015

Re: Convert date to show # of weeks a product is on sale

It would be better to put an sales day of product B together on an sales day of product A.

プロダクトBの販売日をプロダクトAの販売日に合わせるのがよいでしょう。

 

BEAST MODE: comparison_sales_date

case
  when product_name = 'A' then sales_date
  when product_name = 'B' then adddate(sales_date, datediff('2016-01-03',sales_date))
  else sales_date
  end

 

When considering a day of the week, please add the numerical value appropriate to "datediff ('2016-01-03',sales_date)".
もし、曜日を考慮する場合は、「datediff('2016-01-03',sales_date)」に適切な数値を足してみてください。

 

Solution
Accepted by topic author RobynLinden
‎02-08-2017 03:16 PM
Purple Belt
Posts: 119
Registered: ‎04-09-2015

Re: Convert date to show # of weeks a product is on sale

Domoslide1.JPG

 

@RobynLinden hope all is well! I have a solution - takes a few steps but works - depending on what else you are doing this will also allow new products and start dates to be added just by having them show up in the dataset.  The picture above is the output.

 

I used two Magic ETLs to do this.  First one, bring in the data set (Product Test in pics below).  group by Product and add new Column 'Start Date' which is Min(`Sale Date`)

 

Domoslide2.JPG

 

Domoslide3.JPG

 

This creates an output file (product Test 2) that I use in the second Magic ETL 

In the second one bring in original data set and the new data set and link on Product.

Select columns will allow you to only ring one of the product columns and now you have the Sale Date and Start Date in each product line.

 

Domoslide4.JPG

 

Domoslide5.JPG

 

Domoslide6.JPG

 

Use this data set to create the card.  Open beast mode and it is a simple beast mode:

 

Domoslide7.JPG

 

Now you can create the simple card

 

Domoslide8.JPG

 

This works - may be a more elegant way but I hope this helps!

 

@Shevy

Dojo Community Member
** Please like responses by clicking on the thumbs up
** Please Accept / check the answer that solved your problem / answered your question.
Purple Belt
Posts: 191
Registered: ‎04-09-2015

Re: Convert date to show # of weeks a product is on sale

@Shevy this is so smart and almost perfect, but I think I need to rethink the Beast Mode. Currently it's showing negative numbers on the axis - I think because the dataset crosses multiple years, so it's maxing out at Week 52. Both should start at Week 1, and Product A should continue to 138, with Product B stopping at 70.

 

 

I used dummy data before, but Product A's start date was 1/5/2014, and Product B was 10/11/15, and both are still on sale now (last date is stamped 2/5/17). How should I edit?

 

Thank you for all your help! I knew this could be figured out, just couldn't crack it myself. 

 

Capture.PNG

Broadway + Data
Purple Belt
Posts: 119
Registered: ‎04-09-2015

Re: Convert date to show # of weeks a product is on sale

[ Edited ]
glad to see it is close - my simple formula did assume same year... SQL does have DateDiff(ww,Date1,Date2) which should give you difference in weeks but that does not work in Beastmode. You can use this: Round(DATEDIFF(`SaleDate`,`StartDate`)/7,0)+1 That gives you number of days between the sales date and the start date previously added - dividing by seven gives you weeks and the round to 0 decimal places gets you clean numbers. If you want anything that is between 7 and 8 to be 7, then the formula can be: Round(DATEDIFF('SaleDate`,`StartDate`)/7-.05,0)+1 Good Luck!
Dojo Community Member
** Please like responses by clicking on the thumbs up
** Please Accept / check the answer that solved your problem / answered your question.
Dojo Admin
Posts: 618
Registered: ‎01-13-2015

Re: Convert date to show # of weeks a product is on sale

Great conversation among some of our top Dojo experts! Don't forget to mark the solution in the thread so others may benefit from it.

 

Cheers!

Dani

Dojo Admin
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Purple Belt
Posts: 191
Registered: ‎04-09-2015

Re: Convert date to show # of weeks a product is on sale

That did it -- THANK YOU!

Broadway + Data
Announcements
Domo Dojo Day is May 4th! Mark your calendars for the next Domo Dojo Day! To learn more click here! Thanks!