Quarterly analysis

[madhuqliklondon]

Hi friends,

I am trying get the cohort analysis based on quarters. Got data from 1^st apr 2010, where would like to group 2010 to 2012 data as base and work on top in quarters as below attached spread sheet.

I have fields: Customer NO, FirstOrderDate, OrderNo. , OrderValue and Master Calendar built with Year, MonthYear, Month, Week, Quarter,Day.

Please guide to achieve it..

Thanks in advance

[madhuqliklondon]

Yes Sunny.

[madhuqliklondon]

Hi Sunny , I have used that dimension for new customer count ,I have 2 issues , in the fig 1 : I have correct number but want to track customers orders though out till now. for that I am selecting years 2014 to 2017 , see inthe fig 2 , it is accumulating all years new customers and showing repeats in all years .

[sunny_talwar]

Don't think I understand any of what you are saying. May be someone else can offer help here.... Apologize for the inconvenience I have caused thus far

[madhuqliklondon]

May be i am not giving you enough explanation

[madhuqliklondon]

Please check this qvw,

In pivot table, Q1-2012 , new customer count should be 2 only, their order values should be Q1 £80 and Q2 its £50. hope this make sense.

[vishsaggi]

How did you know Q2 should be 50?  I see 100 for Q2? Can you explain a little please?

[madhuqliklondon]

Hi Vish,

For customerid 3 and 4 in q2 they have purchased for 50, with respective order numbers A8 and A9.

Thank you

[vishsaggi]

I think some of the data is missing in your qvw file. I cant see Ordernumber A8 and A9 for Q2 for customerID's 3 and 4. Can you look into that please...?

[madhuqliklondon]

Hi Vish,

Please see the atatchment .Is it not there in the qvw?? I think the confusion is in the first order date and order date. I want group customers on first order date and calculate their sales on order dates.  As 3 and 4 customers first order dates are in Q1 so , want them to be in Q1 group. Now those customers buy something in Q2 that value should come under Q2. Please see the pic. Thank you.

[vishsaggi · Accepted solution]

Are you looking for this?

File attached.