# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for
Did you mean:
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!

 Period Emp ID Dept Stage Hours Mar-15 101 IT 1 25 Mar-15 201 CSE 1 50 Mar-15 301 IT 1 25 Mar-15 101 CSE 1 50 Mar-15 201 IT 1 75 Mar-15 301 CSE 1 45 Mar-15 101 Mechanical 1 50 Mar-15 201 Mechanical 1 25 Mar-15 301 Mechanical 1 35 Mar-15 101 Civil 1 85 Mar-15 201 Civil 1 100 Mar-15 301 Civil 1 150 Mar-15 401 ECE 2 1000 Apr-15 101 IT 1 30 Apr-15 201 CSE 1 55 Apr-15 301 IT 1 30 Apr-15 101 CSE 1 55 Apr-15 201 IT 2 80 Apr-15 301 CSE 1 50 Apr-15 101 Mechanical 1 55 Apr-15 201 Mechanical 1 30 Apr-15 301 Mechanical 1 40 Apr-15 101 Civil 1 90 Apr-15 201 Civil 1 105 Apr-15 301 Civil 1 155
1 Solution

Accepted Solutions
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))

2 Replies
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))

)

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))