Qlikview set analysis with aggregation functions

[lavanya14]

Hi All,

can some one help me on this please. I have added the below expression in the pivot table but couldn't see the total populated.

=if(timestamp(min({< DELAY_ID = {'DELAY'}>}DELAY_START),'DD/MM/YYYY hh:mm') >=  TIMESTAMP(AGGR(MIN({<MACHINE_TYPE= {'ABC'},MOVE = {1} >} MOVETIME),CV_ID,MACHINE_TYPE,DELAY_ID),'DD/MM/YYYY hh:mm') AND
timestamp(max({< DELAY_ID = {'DELAY'}>}DELAY_END),'DD/MM/YYYY hh:mm') <= TIMESTAMP(AGGR(MAX({<MACHINE_TYPE= {'ABC'},MOVE = {1} >} MOVETIME),CV_ID,MACHINE_TYPE,DELAY_ID),'DD/MM/YYYY hh:mm')

Num(sum({< DELAY_ID = {'DELAY'}>}DURATION)/3600000)
,0)

any suggestions are appreciated.

[YoussefBelloum]

Hi,

try to attach some data on excel for example and show the desired output

[sunny_talwar · Accepted solution]

Check here

Sum of rows in pivot tables ‒ QlikView

Basically, you need to add Sum(Aggr()) to make this work

Sum(Aggr(

if(timestamp(min({< DELAY_ID = {'DELAY'}>}DELAY_START),'DD/MM/YYYY hh:mm') >=  TIMESTAMP(AGGR(MIN({<MACHINE_TYPE= {'ABC'},MOVE = {1} >} MOVETIME),CV_ID,MACHINE_TYPE,DELAY_ID),'DD/MM/YYYY hh:mm') AND
timestamp(max({< DELAY_ID = {'DELAY'}>}DELAY_END),'DD/MM/YYYY hh:mm') <= TIMESTAMP(AGGR(MAX({<MACHINE_TYPE= {'ABC'},MOVE = {1} >} MOVETIME),CV_ID,MACHINE_TYPE,DELAY_ID),'DD/MM/YYYY hh:mm')

Num(sum({< DELAY_ID = {'DELAY'}>}DURATION)/3600000)
,0)

,Dimension/s))

[lavanya14]

Many thanks for responding , I really want to add some data but its all confidential and I am not sure to replicate the same scenario as it has got multiple tables info involved in this. I have the shift start and shift end and there are some breaks with in the shift so I am making sure the breaks fall with in the shift range and I finally want to calculate the total hours spent excluding breaks.. that's the reason I am using the if condition. the problem here is when I use this expression in pivot table the totals are not showing up, that's my real problem is.

the expression I gave is partially working but not showing totals.

[lavanya14]

Many thanks for responding , I really want to add some data but its all confidential and I am not sure to replicate the same scenario as it has got multiple tables info involved in this. I have the shift start and shift end and there are some breaks with in the shift so I am making sure the breaks fall with in the shift range and I finally want to calculate the total hours spent excluding breaks.. that's the reason I am using the if condition. the problem here is when I use this expression in pivot table the totals are not showing up, that's my real problem is.

the expression I gave is partially working but not showing totals.

I tried this but unfortunately it didn't work.

[sunny_talwar]

See if this address your confidentiality concerns

Preparing examples for Upload - Reduction and Data Scrambling

[lavanya14]

Hi Sunny,

you are correct, there was some problem with my aggregation and that has resulted in the issue. I corrected it. Thanks for your reply.