1 2 Previous Next 16 Replies Latest reply on May 30, 2017 10:16 PM by Jonathan Chung

Help.. stuck on creating a time series data..

Jonathan Chung May 29, 2017 11:37 PM

I am a bit stuck trying to create a time series based on below product ranking:

Product	Last update	Rank
ABC	04/12/2015	1
ABC	08/08/2016	3
ABC	24/04/2017	2

into a time series so I can monitor the changes in ranking:

Date	Rank
31/01/2015	0
28/02/2015	0
31/03/2015	0
30/04/2015	0
31/05/2015	0
30/06/2015	0
31/07/2015	0
31/08/2015	0
30/09/2015	0
31/10/2015	0
30/11/2015	0
31/12/2015	1
31/01/2016	1
29/02/2016	1
31/03/2016	1
30/04/2016	1
31/05/2016	1
30/06/2016	1
31/07/2016	1
31/08/2016	3
30/09/2016	3
31/10/2016	3
30/11/2016	3
31/12/2016	3
31/01/2017	3
28/02/2017	3
31/03/2017	3
30/04/2017	2
31/05/2017	2

Attache example only has 3 changes in the product lifetime, but in reality is much more.. so I cannot use the excel formula I have in the spreadsheet as I would need to create infinite amount of if-loop. Further complications is that the data will be blended of two tables, so there may be a bit of restrictions on what LOD formula we can use...

Any suggestion will be appreciated.

Many thanks

Jonathan

ProblemI.xlsx 8.8 KB

1. Re: Help.. stuck on creating a time series data..

Prayson Wilfred Daniel May 30, 2017 1:38 AM (in response to Jonathan Chung)
Are you looking for something like this:

I have added XYZ just to show you how to fix on different Products:
{INCLUDE [Product],DATETRUNC('month', [Date]) : SUM([Rank])}

If you do not want to show 0

IIF({INCLUDE [Product],DATETRUNC('month', [Date]) : SUM([Rank])}=0,NULL,{INCLUDE [Product],DATETRUNC('month', [Date]) : SUM([Rank])})

Jonathan Chung.twbx 15.3 KB
Like Show 0 Likes(0)

Actions
2. Re: Help.. stuck on creating a time series data..

Jonathan Chung May 30, 2017 1:26 AM (in response to Prayson Wilfred Daniel)

Hi Prayson

Many thanks for your help, so quick and you make it look so easy, and yet I am still struggling here, LOD concept is still very new to me, my apology for being stupid here.

I tried to follow your formula but modified it as I want to show actual dates:

{INCLUDE[Product], [Last update]:sum([Rank])}

But this doesn't replicate what you did? My formula above only give me 3 dots, I guess I am not sure how you use your "Date" field in your solution, how is that linked to the "Last Updated" field?
Many thanks again for look into this for me.

Regards

Jonathan
Like Show 1 Likes(1)

Actions
3. Re: Help.. stuck on creating a time series data..

Prayson Wilfred Daniel May 30, 2017 1:41 AM (in response to Jonathan Chung)

{INCLUDE[Product],DATETRUNC('month', [Last update]) :sum([Rank])}

I think you are missing DATETRUNC , but I am not quite sure. Can you take a screenshot of what is on Columns and Rows?
Like Show 0 Likes(0)

Actions
4. Re: Help.. stuck on creating a time series data..

Jonathan Chung May 30, 2017 2:09 AM (in response to Prayson Wilfred Daniel)

Hi Prayson

I just tried to add in DateTrunc as well, but it didn't seem to make a differences, here is the Columns/Rows:
Regards

Jonathan
Like Show 1 Likes(1)

Actions
5. Re: Help.. stuck on creating a time series data..

Prayson Wilfred Daniel May 30, 2017 2:23 AM (in response to Jonathan Chung)

Aah! The problem is the type of date you are using. It should be continuous instead of discrete.

Go back to Year and Select DAY in the Continuous part

The colour will change from Blue to Green
Like Show 0 Likes(0)

Actions
6. Re: Help.. stuck on creating a time series data..

Jonathan Chung May 30, 2017 2:43 AM (in response to Prayson Wilfred Daniel)

Hi Prayson

Really appreciate your help.. yeah, my stupid mistake, should have notice that.

I am still not able replicate what you make, I don't understand why. now my formula is as per below:

{INCLUDE[Product], DATETRUNC('month',[Last update]):sum([Rank])}
And I am still only getting 3 marks, not 56 Marks (ok without the XYZ product, probably 28marks), so you are getting the step ladder graphs?
Like Show 1 Likes(1)

Actions
7. Re: Help.. stuck on creating a time series data..

Prayson Wilfred Daniel May 30, 2017 2:58 AM (in response to Jonathan Chung)

Is it not because you are looking at it from Month level instead of Day?
Like Show 0 Likes(0)

Actions
8. Re: Help.. stuck on creating a time series data..

Jonathan Chung May 30, 2017 3:21 AM (in response to Prayson Wilfred Daniel)

hmm.. am afraid not..looking at by day is the same?
Like Show 0 Likes(0)

Actions
9. Re: Help.. stuck on creating a time series data..

Norbert Maijoor May 30, 2017 3:20 AM (in response to Jonathan Chung)

Hi guys,

Following the thread I noticed the solution provided by Prayson Wilfred Daniel is based on a dataset with entries for each day where

your dataset has only 3 entries????

Regards,
Norbert
Like Show 0 Likes(0)

Actions
10. Re: Help.. stuck on creating a time series data..

Prayson Wilfred Daniel May 30, 2017 3:28 AM (in response to Jonathan Chung)

Can you right-click on your chart and view Data?

What do you see?
Like Show 0 Likes(0)

Actions
11. Re: Help.. stuck on creating a time series data..

Jonathan Chung May 30, 2017 3:57 AM (in response to Prayson Wilfred Daniel)

My apologies both.. I didn't see Prayson's workbook in the 1st reply, thought it was only a print screen.. yes, the original questions is how to convert the input (3 entries that I had) into the time series (ie rank data per day), based on the 'last update' field. ie if the date falls in between the previous update and last update, then that date rank should remain as previous rank.

Sorry that we have been going around in circle!!
Like Show 0 Likes(0)

Actions
12. Re: Help.. stuck on creating a time series data..

Prayson Wilfred Daniel May 30, 2017 3:48 AM (in response to Norbert Maijoor)

Hej Norbert. They are two data source in one excel sheet.
Like Show 0 Likes(0)

Actions
13. Re: Help.. stuck on creating a time series data..

Prayson Wilfred Daniel May 30, 2017 3:52 AM (in response to Jonathan Chung)

I think you might have missed data preparation part, thanks to Norbert Maijoor .

I merged the two source to produce:

Product Date Rank
ABC 27-02-2015 0
ABC 28-02-2015 0
ABC 31-03-2015 0
ABC 30-04-2015 0
ABC 31-05-2015 0
ABC 30-06-2015 0
ABC 31-07-2015 0
ABC 31-08-2015 0
ABC 30-09-2015 0
ABC 31-10-2015 0
....
Like Show 0 Likes(0)

Actions
14. Re: Help.. stuck on creating a time series data..

Norbert Maijoor May 30, 2017 3:57 AM (in response to Prayson Wilfred Daniel)

Hi Prayson,

My interpretation was the following..... the file with the 3 entries as starting point and the second file as the required result;).

Regards,
Norbert
Like Show 0 Likes(0)

Actions