Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Specialist
Specialist

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

)

Highlighted
Specialist
Specialist

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