Calculating the quarter difference for the same customer

[#1]

Hi all,

I have the following scripts and dataset as follow and now i need to find the quarter difference for the same customer.

can anyone advise me how to get the quarter difference and what should i add on?

1. SET ThousandSep=','; 
2. SET DecimalSep='.'; 
3. SET MoneyThousandSep=','; 
4. SET MoneyDecimalSep='.'; 
5. SET MoneyFormat='$#,##0.00;($#,##0.00)'; 
6. SET TimeFormat='h:mm:ss TT'; 
7. SET DateFormat='DD-MM-YY'; 
8. SET TimestampFormat='DD-MM-YY h:mm:ss[.fff] TT'; 
9. SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'; 
10. SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'; 
11. SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December'; 
12. SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday'; 
13. SET FirstWeekDay=6; 
14. SET BrokenWeeks=1; 
15. SET ReferenceDay=0; 
16. SET FirstMonthOfYear=1; 
17. SET CollationLocale='en-US'; 
18.  
19. table1: 
20. LOAD Date(MonthStart(Date#(ScreeningMonth, 'MMM-YY')), 'MMM-YY') as ScreeningMonth, 
21. ScreeningQtr; 
22. LOAD * Inline [ 
23.     ScreeningMonth, ScreeningQtr 
24.     Apr-15, Q1 
25.     May-15, Q1 
26.     Jun-15, Q1 
27.     Jul-15, Q2 
28.     Aug-15, Q2 
29.     Sep-15, Q2 
30.     Oct-15, Q3 
31.     Nov-15, Q3 
32.     Dec-15, Q3 
33. ]; 
34.  
35. table2: 
36. LOAD [S/N], 
37.     Customer, 
38.     ScreeningDate, 
39.     Date(MonthStart(ScreeningDate), 'MMM-YY') as ScreeningMonth 
40. from XXXX

[swuehl]

How is the "the quarter difference" defined for a given customer?

When looking at your sample table, a Customer can show multiple records, so what is "the difference" in these cases?

Also, what about a date range spanning multiple years, how do you want to handle this?

If possible, post some sample records and your requested result.

And last, have you had a look at my last reply to your other thread?

It seems to me that you are basically posting multiple times the same request, which is not a good idea, since it makes it hard to follow the discussion and further clutters the forum with duplicate topics.

Regards,

Stefan

[amayuresh]

Try This:

in script add:

dual(year(OrderDate)&'Q'&Ceil(Month(OrderDate)/3),QuarterStart(OrderDate))as MyQuarter,

Create straight table:

Dimension as Customer

Expression as

Curr Q Sales =Sum(Sales)

Pre Q Sales = Sum({1<MyQuarter={"$(=dual(year(max(OrderDate))&'Q'&Ceil((Month(max(OrderDate))/3)-1),QuarterStart(max(OrderDate))))"}>}Sales)

[tyagishaila]

What do you mean by "quarter difference" here?

[#5]

Like q3 - Q1= 2q.

The 2q is quarter difference

Sent from Outlook Mobile<https://aka.ms/blhgte>

[amayuresh]

Try using below expression

Curr Q Sales =Sum(Sales)

Pre Q Sales = Sum({1<MyQuarter={"$(=dual(year(max(OrderDate))&'Q'&Ceil((Month(max(OrderDate))/3)-1),QuarterStart(max(OrderDate))))"}>}Sales)

[tyagishaila]

Tab:

LOAD * INLINE [

    ScreeningQtr, Customer

    Q3, A

    Q3, B

    Q3, C

    Q3, D

    Q3, E

    Q3, F

    Q3, A

    Q2, B

    Q1, C

    Q2, D

    Q1, E

    Q1, F

    Q1, A

    Q1, B

    Q2, C

] ;

Load *,  If(Previous(Customer) = Customer ,right(ScreeningQtr,1)-Right(Previous(ScreeningQtr),1), right(ScreeningQtr,1)) As QtrNo

Resident Tab Order by Customer, ScreeningQtr asc;

Drop Table Tab;

[#8]

HI,

I cannot define another inline using customers as the customer list is too many and will grow over the time.

i have tried to identify the diff between the same customer.

However, the below script failed. anyone can advise, pls?

Data:

Load

*,

if(previous([Customer]) = [Customer], ceil((ScreeningQtr - Previous(ScreeningQtr)))) As Diff;

Load

*

Resident Table2

Order by [Customer], ScreeningQtr;

Drop Table Table2;

[#9]

can't work.

i need to find the quarter difference for the same customer.

for example Customer A - Q3 and Customer A - Q7

the quarter difference for Customer A shall be 4.

Able to advise, pls.