Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prabhu0505
Specialist
Specialist

Compare between two periods and find the difference

Hi,

Below table has two periods and for highlighted records stage changes from period 1 (Mar-15) to period 2 (
Apr-15). The difference is 5, I need to write a set analysis expression for getting this.

Catch is: I have to consider only those records, which ones EMP ID are repeated in period 2 and Stage must have changed.

Tried something like,

=sum({[Waterfall2]<[Emp ID]=P({[Waterfall1]} [Emp ID]), Stage=P({Waterfall1} [Emp ID])+P({Waterfall1} [Stage])>} Hours)

Didn't work out. Appreciate any help on this. Thanks!

PeriodEmp IDDeptStageHours
Mar-15101IT125
Mar-15201CSE150
Mar-15301IT125
Mar-15101CSE150
Mar-15201IT175
Mar-15301CSE145
Mar-15101Mechanical150
Mar-15201Mechanical125
Mar-15301Mechanical135
Mar-15101Civil185
Mar-15201Civil1100
Mar-15301Civil1150
Mar-15401ECE21000
Apr-15101IT130
Apr-15201CSE155
Apr-15301IT130
Apr-15101CSE155
Apr-15201IT280
Apr-15301CSE150
Apr-15101Mechanical155
Apr-15201Mechanical130
Apr-15301Mechanical140
Apr-15101Civil190
Apr-15201Civil1105
Apr-15301Civil1155
1 Solution

Accepted Solutions
prabhu0505
Specialist
Specialist
Author

Thanks! t.chetirbok

Below expression gave me the result.

=sum(aggr

(if(

only({Waterfall1}[Emp ID])=only({Waterfall2}[Emp ID]) and only({Waterfall1}Dept)=only({Waterfall2}Dept) and

only({Waterfall1}Stage)<only({Waterfall2}Stage)

,fabs( only({Waterfall1}Hours)-only({Waterfall2}Hours))

), [Emp ID], Dept))

View solution in original post

2 Replies
t_chetirbok
Specialist
Specialist

Hello!

if i understood you correctly, you want something like this

you can use expression:

=if(

only({Waterfall1}Stage)<>only({Waterfall2}Stage)

,fabs( only({Waterfall1}Hours)-only({Waterfall2}Hours))

)

prabhu0505
Specialist
Specialist
Author

Thanks! t.chetirbok

Below expression gave me the result.

=sum(aggr

(if(

only({Waterfall1}[Emp ID])=only({Waterfall2}[Emp ID]) and only({Waterfall1}Dept)=only({Waterfall2}Dept) and

only({Waterfall1}Stage)<only({Waterfall2}Stage)

,fabs( only({Waterfall1}Hours)-only({Waterfall2}Hours))

), [Emp ID], Dept))