Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
varunvarma
Contributor III
Contributor III

Pivot Table with current and previous months

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 monthPrevious month
JanD186
D2810
D356
FebD1118
D268
D3145
MarD11011
D2146
D32014
1 Solution

Accepted Solutions
Kushal_Chawda


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;


View solution in original post

6 Replies
Kushal_Chawda


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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

Thank u bro...I got it.

Kushal_Chawda

that's great

kkkumar82
Specialist III
Specialist III

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