Products sales from launch date

[kira_whopper]

Hi there,

I'm having issue regarding the following situation:

I need to show in a line chart or straight table the sales of each product starting from it's launch date.

The rules are:
- Chart/Table must consider only Products with Launch Date more recent then 24 months;

- Each line/row mus show the Sales value of the product's respective month. For example, Product1, released in 2016-01, will show in "Sales 1st Month" the Sales of 2016-01, Product2, release in 2015-08, will show in "Sales 1st Month" the Sales of 2015-08. The obective is to compare each product's start among the market.

I'm sending attached a qvw file and a xls sheet with an example of what I need.

In the qvw bottom, there are 5 straight tables with what I need to do.

Then, in the middle there's a straight table titled "Launch Sales", which should display all the products with the respective sales for each, from the 1st to the 6th month after its launch date.

Thanks in advance!

[kira_whopper]

One last thing, is it possible to use a dynamic rank for the FirstSortedValue()?

For example, in the expression of the second Month:

FirstSortedValue({<SalesDate = {"$(='>=' & Only([Launch Date SKU]))"}, SalesMonth, SalesYear>}Aggr(Sum({<SalesDate = {"$(='>=' & Only([Launch Date SKU]))"}, SalesMonth, SalesYear>}SalesValue), SKU, [Launch Date SKU], SalesDate),

Aggr(Only({<SalesDate = {"$(='>=' & Only([Launch Date SKU]))"}, SalesMonth, SalesYear>}SalesDate), SKU, [Launch Date SKU], SalesDate), 2)

Change this last 2 to a calculated value.

I tried using the formula:

1+((year(Only([Launch Date SKU]))*12)+month(Only([Launch Date SKU])))-(((year(Min({1}SalesDate))*12)+month(Min({1}SalesDate))))

which gives me the difference in months between the Launch Date to the First Calendar Month.

And some variations with Aggr and variables, but it was no go.

The idea is to show in the Sales 1st Month, the sales value of each product's sale value in its respective first month. With the last formula I send you, all Products consider only one month as start, but each one should have their.

Sample following attached.

[sunny_talwar]

I think it would be helpful to know what the expected output here is? Would you be able to provide an Excel file with the expected output?

[kira_whopper]

Sure!

Following attached

It`s in the second sheet.

The 1st month is the first month of sales regarding the launch date of the respective product, then the 2nd, 3rd as the same.

[kira_whopper · Accepted solution]

I've managed using a new field in the table that calculates the difference between the launch date and the month-year of sales.

This is the field created:

Fact:

LOAD

  *,

  ((year(SalesDate)*12)  +  month(SalesDate))  -

  (((year([Launch Date]))*12)  +  month([Launch Date])) as [Launch Date Diff]

Resident Fact_Tmp;

And this is how it worked in the formula:

sum({<

  [Launch Date] = {">=$(=Date(AddMonths(Max(YearMonth), -23), 'MMM-YY'))<=$(=Date(Max(YearMonth), 'MMM-YY'))"},

  [Launch Date Diff] = {0-n},

  Year=, Month=

  >} SalesValue)

0-n is the Month I would like to display (First, second, etc)

Anyway, your formula helped a lot! So I'll keep it as the right answer.

[sunny_talwar]

This is awesome, thanks for sharing this information. I will suggest to actually mark your own response as correct as this may be helpful for someone else in the future. By seeing my response as correct, they might never see this last response. So I recommend changing the correct answer to something that actually worked.

Best,

Sunny

[kira_whopper]

Done!

Thanks a lot, Sunny!

[sunny_talwar]

Thank you for a chance to learn a new thing here

Best,

Sunny