topic Re: qtr comparisoin in QlikView

qtr comparisoin

soha1902 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to compare current year quarter vs previous year quarter values:

QTR_ID	Sales
2013Q1	100
2013Q2	200
2013Q3	300
2013Q4	150
2014Q1	200
2014Q2	110
2014Q3	120
2014Q4	140
2015Q1	160
2015Q2	300
2015Q3	500
2015Q4	400

for eg 2015Q4 vs 2014Q4

Can any one help me to create a tabular report for this???

Re: qtr comparisoin

Anonymous — Thu, 17 Mar 2016 07:18:10 GMT

refer below link.....

Re: Curr qtr vs prev qtr

Re: qtr comparisoin

its_anandrjs — Thu, 17 Mar 2016 07:37:04 GMT

You can try this way also

LOAD *,Left(QTR_ID,4) as Year,Right(QTR_ID,1) as QtrMax;

LOAD * Inline

[

QTR_ID, Sales

2013Q1, 100

2013Q2, 200

2013Q3, 300

2013Q4, 150

2014Q1, 200

2014Q2, 110

2014Q3, 120

2014Q4, 140

2015Q1, 160

2015Q2, 300

2015Q3, 500

2015Q4, 400

];

In any straight table

Dim:- QTR_ID

Expre:- sum( {<Year= {"$(=max(Left(QTR_ID,4)))"},QtrMax ={"$(=max(right(QTR_ID,1)))"} >}Sales)

Expre:- sum( {<Year= {"$(=max(Left(QTR_ID,4))-1)"},QtrMax ={"$(=max(right(QTR_ID,1))-1)"} >}Sales)

Regards

Anand

Re: qtr comparisoin

soha1902 — Thu, 17 Mar 2016 07:41:39 GMT

What is this "QtrMax" field?? It is not available.

Re: qtr comparisoin

its_anandrjs — Thu, 17 Mar 2016 07:45:30 GMT

It is calculated field to finding max quarter value in filed QtrMax

Regards

Anand

Re: qtr comparisoin

its_anandrjs — Thu, 17 Mar 2016 07:54:41 GMT

Try this expression please with some changes

Dim:- QTR_ID

Expre:- sum( {<Year= {"<=$(=max(Left(QTR_ID,4))) >=$(=max(Left(QTR_ID,4))-1)"},QtrMax ={"$(=max(right(QTR_ID,1)))"} >}Sales)

Regards,

Anand

Re: qtr comparisoin

jagan — Thu, 17 Mar 2016 07:57:51 GMT

Hi,

Try like this

LOAD

Left(QTR_ID,4) AS Year,

MakeDate(Left(QTR_ID,4), Right(QTR_ID,1) * 3) AS Date

FROM DataSource;

Now use below expressions:

Current Quarter:

Sum({<Year=, Date={'>=$(=QuarterStart(Max(Date)))<=$(=QuarterEnd(Max(Date)))'}>} Sales)

Previous Quarter:

Sum({<Year=, Date={'>=$(=QuarterStart(Max(Date), -1))<=$(=QuarterEnd(Max(Date), -1))'}>} Sales)

Hope this helps you.

Regards,

Jagan.

Re: qtr comparisoin

sunny_talwar — Thu, 17 Mar 2016 08:25:38 GMT

Something like this?

Script:

Table:

LOAD Dual(QTR_ID, PurgeChar(QTR_ID, 'Q')) as QTR_ID,

Sales

Inline [

QTR_ID, Sales

2013Q1, 100

2013Q2, 200

2013Q3, 300

2013Q4, 150

2014Q1, 200

2014Q2, 110

2014Q3, 120

2014Q4, 140

2015Q1, 160

2015Q2, 300

2015Q3, 500

2015Q4, 400

];

NewTable:

LOAD *,

AutoNumber(QTR_ID) as Sort

Resident Table

Order By QTR_ID;

DROP Table Table;

[As-Of Table]:

LOAD QTR_ID as [AS-OF],

Sort,

'CQ' as Flag

Resident NewTable;

Concatenate ([As-Of Table])

LOAD QTR_ID as [AS-OF],

Sort - 1 as Sort,

'PQ' as Flag

Resident NewTable;

Expression for current quarter: =Sum({<Flag = {'CQ'}>}Sales)

Expression for last quarter: =Sum({<Flag = {'PQ'}>}Sales)

Re: qtr comparisoin

jagan — Thu, 17 Mar 2016 08:26:59 GMT

For getting last year same quarter use

Sum({<Year=, Date={'>=$(=QuarterStart(Max(Date), -4))<=$(=QuarterEnd(Max(Date), -4))'}>} Sales)

Regards,

jagan.