topic Re: Qlikview set analysis with aggregation functions in QlikView

Qlikview set analysis with aggregation functions

lavanya14 — Fri, 19 Jan 2018 09:58:11 GMT

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.

Re: Qlikview set analysis with aggregation functions

YoussefBelloum — Fri, 19 Jan 2018 10:15:21 GMT

Hi,

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

Re: Qlikview set analysis with aggregation functions

sunny_talwar — Fri, 19 Jan 2018 12:28:44 GMT

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

Re: Qlikview set analysis with aggregation functions

lavanya14 — Fri, 19 Jan 2018 12:43:57 GMT

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.

Re: Qlikview set analysis with aggregation functions

lavanya14 — Fri, 19 Jan 2018 12:44:25 GMT

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

I tried this but unfortunately it didn't work.

Re: Qlikview set analysis with aggregation functions

sunny_talwar — Fri, 19 Jan 2018 12:47:15 GMT

See if this address your confidentiality concerns

Preparing examples for Upload - Reduction and Data Scrambling

Re: Qlikview set analysis with aggregation functions

lavanya14 — Tue, 23 Jan 2018 13:43:07 GMT

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.