topic Re: Calculating the quarter difference for the same customer in QlikView

Calculating the quarter difference for the same customer

Wed, 25 Nov 2020 16:16:04 GMT

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?

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD-MM-YY';
SET TimestampFormat='DD-MM-YY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
table1:
LOAD Date(MonthStart(Date#(ScreeningMonth, 'MMM-YY')), 'MMM-YY') as ScreeningMonth,
ScreeningQtr;
LOAD * Inline [
ScreeningMonth, ScreeningQtr
Apr-15, Q1
May-15, Q1
Jun-15, Q1
Jul-15, Q2
Aug-15, Q2
Sep-15, Q2
Oct-15, Q3
Nov-15, Q3
Dec-15, Q3
];
table2:
LOAD [S/N],
Customer,
ScreeningDate,
Date(MonthStart(ScreeningDate), 'MMM-YY') as ScreeningMonth
from XXXX

Re: Calculating the quarter difference for the same customer

swuehl — Mon, 28 Mar 2016 17:48:18 GMT

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

Re: Calculating the quarter difference for the same customer

amayuresh — Tue, 29 Mar 2016 06:09:39 GMT

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)

Re: Calculating the quarter difference for the same customer

tyagishaila — Tue, 29 Mar 2016 06:16:09 GMT

What do you mean by "quarter difference" here?

Re: Calculating the quarter difference for the same customer

Tue, 29 Mar 2016 06:34:24 GMT

Like q3 - Q1= 2q.

The 2q is quarter difference

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

Re: Calculating the quarter difference for the same customer

amayuresh — Wed, 30 Mar 2016 03:00:13 GMT

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)

Re: Calculating the quarter difference for the same customer

tyagishaila — Wed, 30 Mar 2016 05:13:20 GMT

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;

Re: Calculating the quarter difference for the same customer

Wed, 27 Apr 2016 02:24:47 GMT

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;

Re: Calculating the quarter difference for the same customer

Wed, 27 Apr 2016 03:14:44 GMT

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.