topic Re: Products sales from launch date in QlikView

Products sales from launch date

kira_whopper — Tue, 30 Aug 2016 17:21:43 GMT

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!

Re: Products sales from launch date

sunny_talwar — Tue, 30 Aug 2016 18:21:22 GMT

Is this what the goal is?

Re: Products sales from launch date

kira_whopper — Tue, 30 Aug 2016 19:05:46 GMT

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

Re: Products sales from launch date

sunny_talwar — Tue, 30 Aug 2016 19:06:30 GMT

Awesome

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

Best,

Sunny

Re: Products sales from launch date

kira_whopper — Tue, 30 Aug 2016 20:37:26 GMT

Hi again, Sunny...

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

Re: Products sales from launch date

sunny_talwar — Tue, 30 Aug 2016 20:46:03 GMT

Like the attached?

Re: Products sales from launch date

kira_whopper — Tue, 30 Aug 2016 21:18:38 GMT

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

Thanks again!!

Re: Products sales from launch date

sunny_talwar — Tue, 30 Aug 2016 21:31:21 GMT

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

Re: Products sales from launch date

kira_whopper — Wed, 31 Aug 2016 12:34:25 GMT

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

Re: Products sales from launch date

sunny_talwar — Wed, 31 Aug 2016 12:36:22 GMT

Got it...

Re: Products sales from launch date

kira_whopper — Wed, 31 Aug 2016 19:09:31 GMT

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.

Re: Products sales from launch date

sunny_talwar — Wed, 31 Aug 2016 19:12:47 GMT

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?

Re: Products sales from launch date

kira_whopper — Wed, 31 Aug 2016 19:22:57 GMT

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.

Re: Products sales from launch date

kira_whopper — Thu, 01 Sep 2016 19:01:52 GMT

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.

Re: Products sales from launch date

sunny_talwar — Thu, 01 Sep 2016 19:04:40 GMT

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

Re: Products sales from launch date

kira_whopper — Thu, 01 Sep 2016 19:38:23 GMT

Done!

Thanks a lot, Sunny!

Re: Products sales from launch date

sunny_talwar — Thu, 01 Sep 2016 20:49:42 GMT

Thank you for a chance to learn a new thing here

Best,

Sunny