Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Continuous cumulative count based on discrete dates

Hi all,

 

I have a table with customer IDs, and the date of their first, second, third, and fourth car purchases. (Some customers might not have four purchases, for them the dates would be 2099, data shown below) 

 

What I need is, for each day, I would like to know how many customers have one car, how many customers have two cars etc. Basically I want to plot the distribution of customers based on how many cars they have against time, and this plot should cover all days from first purchase date until today. For example, first car is purchased on 12th Feb 2019

On this date %100 of my customers have one car. 1 day later on 13th Feb, still %100 of my customers have one car.

On 15th Feb 2019, same customer purchases a car again, so %100 of my customers have two cars. 

On 3rd October, 2019 another customer purchases a car, now I have two customers, one of them have one car and one of them have two cars, and I would like to plot their distribution.

 

Basically my X axis should be the dates, and on Y axis I would like to have percentage of customers who have one car, two cars and so on. Would appreciate the help so much! Thanks in advance

 

Customer_IDFirst_PurchaseSecond_PurchaseThird_PurchaseFourth_Purchase
712 Feb 201915 Feb 201922 May 201925 May 2019
53 Oct 20191 Jan 20991 Jan 20991 Jan 2099
328 Dec 201929 Mar 202031 Mar 20231 Jan 2099
916 Jan 202020 Nov 20211 Jan 20991 Jan 2099
830 Sep 20209 Aug 202118 Dec 20211 Jan 2099
46 Dec 202023 Sep 20211 Jan 20991 Jan 2099
111 Jan 202122 Sep 20211 Jan 20991 Jan 2099
614 Jan 202222 Dec 202222 Feb 20231 Jan 2099
227 Jun 202327 Jun 20231 Jan 20991 Jan 2099
  • Hi,

    Go To Home > Transform Data.  click on The Categories query and double click on Source.  Make changes there and click on OK.  Click on Close and Apply.

    Hope this helps.

12 Replies