Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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