Highlighted
Blue Belt

## 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?

Accepted Solutions
Purple Belt

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

@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`)

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.

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

Now you can create the simple card

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

All Replies
Blue Belt

## 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)」に適切な数値を足してみてください。

Purple Belt

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

@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`)

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.

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

Now you can create the simple card

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
Blue Belt

## 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.

Purple Belt

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

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

## 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