topic Re: Curr qtr vs prev qtr in QlikView

Curr qtr vs prev qtr

Mon, 14 Mar 2016 13:03:33 GMT

HI All,

Need to create current vs previous qurter..

For Example:

If I select 2016 Q1 then Output should be --- 2016 Q1 & 2015 Q4.

If I select 2015 Q3 then Output should be --- 2015 Q3 & 2015 Q2.

Kindly help me on this..

Regards,

Helen

Re: Curr qtr vs prev qtr

Gysbert_Wassenaar — Mon, 14 Mar 2016 13:17:50 GMT

Create a numeric quarter field, for example a date:

Dual( Year(MyDate) & 'Q' & Ceil(Month(MyDate)/3), QuarterStart(MyDate)) as MyQuarter

Then create two expressions:

Current Quarter: Sum(Amount)

Previous Quarter: Sum({<MyQuarter#={'$(=QuarterStart(Max(MyQuarter),-1))'}>}Amount)

And if it doesn't work please post a small qlikview document with some example data.

Re: Curr qtr vs prev qtr

jonathandienst — Mon, 14 Mar 2016 13:21:40 GMT

In your calendar, add Quarter sequence number - something like"

...

Year(Caldate) * 4 + Floor(Month(Caldate) / 4) As QSeq,

...

Now you can use a simple set expression:

Current quarter:

Sum({<QSeq = {"$(=Max(QSeq))"}>} Amount)

Previous quarter

Sum({<QSeq = {"$(=Max(QSeq)-1)"}>} Amount)

(replace CalDate with the date field in your calendar and adapt the above expression to your requirements)

Re: Curr qtr vs prev qtr

Mon, 14 Mar 2016 14:51:24 GMT

attached the excel & app as above

Regards,

Helen

Re: Curr qtr vs prev qtr

Gysbert_Wassenaar — Mon, 14 Mar 2016 16:20:51 GMT

See attached example.

Re: Curr qtr vs prev qtr

Mon, 14 Mar 2016 17:02:47 GMT

Thanks..

With out any selection also i need to See Current quarter & Previous Quarter

Regards,

Helen

Re: Curr qtr vs prev qtr

Gysbert_Wassenaar — Mon, 14 Mar 2016 17:09:23 GMT

Then make sure your data contains data from 2016. The document you posted doesn't.

Re: Curr qtr vs prev qtr

Mon, 14 Mar 2016 18:29:25 GMT

Make it default current quarter & previous quarter..

Regards,

Helen

Re: Curr qtr vs prev qtr

Mon, 14 Mar 2016 18:30:25 GMT

Based on the given data .Make it default current quarter & previous quarter..

Regards,

Helen

Re: Curr qtr vs prev qtr

Gysbert_Wassenaar — Mon, 14 Mar 2016 18:47:19 GMT

Can't. Current quarter is the first quarter of 2016. Your document does not contain data from 2016.

Re: Curr qtr vs prev qtr

Tue, 15 Mar 2016 03:40:35 GMT

Based on the given data 2009 is the max year, will see Max qtr & Prev qtr

Regards,

Helen

Re: Curr qtr vs prev qtr

Tue, 15 Mar 2016 04:54:25 GMT

Current Quarter: Sum(Amount)

Previous Quarter: Sum({<MyQuarter#={'$(=QuarterStart(Max(MyQuarter),-1))'}>}Amount)

Re: Curr qtr vs prev qtr

MayilVahanan — Tue, 15 Mar 2016 05:15:06 GMT

PFA

Re: Curr qtr vs prev qtr

Anonymous — Tue, 15 Mar 2016 05:45:31 GMT

Hi Helen,

try to use Master Calender refer below code...

T1:

LOAD OrderID,

ProductID,

Product,

CustomerID,

EmployeeID,

OrderDate,

OrderDate as #DateKey,

Margin,

Sales,

Cost,

Quantity,

F11

FROM

(biff, embedded labels, table is Orders$);

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q1

2, Q1

3, Q1

4, Q2

5, Q2

6, Q2

7, Q3

8, Q3

9, Q3

10, Q4

11, Q4

12, Q4

];

MonthMap:

Mapping LOAD * Inline [

MonthNo, LongMonth

1, January

2, February

3, March

4, April

5, May

6, June

7, July

8, August

9, September

10, October

11, November

12, December

];

Temp:

Load

min(OrderDate) as minDate,

max(OrderDate) as maxDate

Resident T1;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(YearEnd(Today()));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Set vFM = 4 ;

MasterCalendar:

LOAD

TempDate AS #DateKey,

TempDate AS #HDateKey,

week(TempDate) as MasterWeek,

Year(TempDate) as MasterYear,

Year(TempDate) -1 as [MasterPriorYear],

Month(TempDate) as MasterMonth,

MonthStart(TempDate) as StartDate,

MonthEnd (TempDate) as EndDate,

Day(TempDate) as MasterDay,

YeartoDate(TempDate)*-1 as MasterCurYTDFlag,

YeartoDate(TempDate,-1)*-1 as MasterLastYTDFlag,

ApplyMap('QuartersMap', month(TempDate), Null()) as MasterQuarter ,

Autonumberhash256 (ApplyMap('QuartersMap', month(TempDate), Null()),Year(TempDate))as QuarterNumber

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Sum({<QuarterNumber={'$(=Max(QuarterNumber))'}>}Sales)

sum({1<QuarterNumber={'$(=Max(QuarterNumber)-1)'}>}Sales)

Re: Curr qtr vs prev qtr

amayuresh — Tue, 15 Mar 2016 07:33:08 GMT

you can try this,

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

Create straight table: Dimension as Employee ID

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)