topic Re: Pivot Table with current and previous months in QlikView

Pivot Table with current and previous months

varunvarma — Sat, 02 Jan 2016 09:55:17 GMT

Dear All,

Greetings!!!

Please find the sample data in the attachment.

I need to create a pivot table with dimensions

Dim 1 : Current year Months (month(DATE1))

Dim 2 : Dept Field (DEPT1)

and two expressions

Exp1 : Count(EMPID) wrt month

Exp2 : Count(EMPID) wrt previous month.

O/P should be like this:

MONTH	DEPT1	Current month	Previous month
Jan	D1	8	6
	D2	8	10
	D3	5	6
Feb	D1	11	8
	D2	6	8
	D3	14	5
Mar	D1	10	11
	D2	14	6
	D3	20	14

Re: Pivot Table with current and previous months

Kushal_Chawda — Sat, 02 Jan 2016 10:38:17 GMT

Data:

LOAD date(DATE1,'DD-MM-YYYY') as DATE1,

DEPT1,

EMPID

FROM

(ooxml, embedded labels, table is Sheet2);

Temp:

LOAD min(DATE1) as MinDate,

max(DATE1) as MaxDate

Resident Data;

Calender:

LOAD *,

year(DATE1) as Year,

month(DATE1) as Month,

monthname(DATE1) as MonthYear;

LOAD date(MinDate+IterNo()-1,'DD-MM-YYYY') as DATE1

Resident Temp

While MinDate+IterNo()-1<=MaxDate;

DROP Table Temp;

Re: Pivot Table with current and previous months

varunvarma — Sat, 02 Jan 2016 10:52:13 GMT

Thank you so much for the help. But I forgot to mention this point. I need to achieve o/p in front end only. I should not change data model. Please let me know if any possible way. Thank you.

Re: Pivot Table with current and previous months

varunvarma — Sat, 02 Jan 2016 11:04:58 GMT

Thank u bro...I got it.

Re: Pivot Table with current and previous months

Kushal_Chawda — Sat, 02 Jan 2016 13:09:28 GMT

that's great

Re: Pivot Table with current and previous months

kkkumar82 — Sat, 02 Jan 2016 15:32:47 GMT

Its very nice solution, just one doubt, how above function works in this situation a little bit confused, if you take for eg

Month January how to takes the values of December Department Wise, can you explain a bit.

Thanks

Kiran Kumar

Re: Pivot Table with current and previous months

Kushal_Chawda — Sat, 02 Jan 2016 17:07:07 GMT

Above function works based on the dimension values. In this case above function picks the value based on the department & month.

So Data is aggregated like below

D1- Jan

D1-Feb

D1-Mar

D1-Dec

D2-Jan

D2-Feb

So on

Now above will pick the value which is one row above

So for D1-Feb above will pick the value of D1-Jan