Solved: Re: Products sales from launch date - Qlik Community

kira_whopper · ‎2016-08-30

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 · ‎2016-09-01

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.

View solution in original post

sunny_talwar · ‎2016-08-30

Is this what the goal is?

kira_whopper · ‎2016-08-30

Thanks a lot, that's exactly it!

In my case, I need to show 1st month, even if it's 0, so my expression went like this:

FirstSortedValue(Aggr(Sum(SalesValue), SKU, [Launch Date SKU], SalesDate),

Aggr(Min(SalesDate), SKU, [Launch Date SKU], SalesDate))

withou the if

o/

sunny_talwar · ‎2016-08-30

Awesome

I am glad it helped you move in the right direction.

Best,

Sunny

kira_whopper · ‎2016-08-30

Hi again, Sunny...

Any ideas on how to make it work keeping one value of Year/Month or SalesDate selected?

sunny_talwar · ‎2016-08-30

Like the attached?

kira_whopper · ‎2016-08-30

Elegant solution. I tried using Only instead of Min, but couldn't make it work out...

Thanks again!!

sunny_talwar · ‎2016-08-30

You tried Only here?

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

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

I think only will work if you have made a single selection, Min will work with multiple selections in SalesDate field

kira_whopper · ‎2016-08-31

In my case, I used it with the Launch Date as reference:

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

sunny_talwar · ‎2016-08-31

Got it...