Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
Hi,
try to attach some data on excel for example and show the desired output
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))
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.
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.
See if this address your confidentiality concerns
Preparing examples for Upload - Reduction and Data Scrambling
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.