Pivot Table with current and previous months

[varunvarma]

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

[Kushal_Chawda · Accepted solution]

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;

[varunvarma]

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.

[varunvarma]

Thank u bro...I got it.

[Kushal_Chawda]

that's great

[kkkumar82]

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

[Kushal_Chawda]

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