topic Re: Calculation based on dimension selected in QlikView

Calculation based on dimension selected

harson — Tue, 18 Apr 2017 09:39:29 GMT

Hello Community,

I have a problem on QlikView at this time,

I have to calculate the working times (DT) to suit the selected pause types.

First I have to create check boxes for my different types of pause, like this:

☐ Avec Pause Administrative

☐ Avec Pause toilette

☐ Avec Pause déjeuner

☐ Avec Pause 15min

The formula for working time (TD) is : DT = [Work_duration] – [pause_duration] with pause_duration is the value of pause_duration for the checked boxes.

For example :

In 03/04/2017

- If I check « Avec Pause Adminitrative »

=> DT = [Work_duration]-[pause_duration] for the pause_code= PA

ð DT = 158 :25 :00 – 04 :30 :00

- If I check « Avec Pause Adminitrative », « Avec Pause toilette »

=> DT = [Work_duration]-[pause_duration] for the pause_code = PA et PT

ð DT = 158 :25 :00 – (04 :30 :00 + 04 :20 :00)

- And if I don’t check anything on the ticked boxes DT= [Work_duration]

Any idea ?

Thanks in advance.

This is my data :

Pause :
LOAD * INLINE [
     Date, pause_duration, pause_code
     03/04/2017, 04:30:00, PA
     04/04/2017, 03:15:00, PA
     05/04/2017, 04:48:00, PA
     06/04/2017, 03:21:00, PA
     07/04/2017, 03:54:00, PA
     03/04/2017, 04:20:00, PT
     04/04/2017, 04:15:00, PT
     05/04/2017, 04:48:00, PT
     06/04/2017, 04:21:00, PT
     07/04/2017, 20:15:00, PT
     03/04/2017, 19:15:00, PD
     04/04/2017, 19:53:00, PD
     05/04/2017, 18:04:00, PD
     06/04/2017, 19:53:00, PD
     07/04/2017, 19:25:00, PD
     03/04/2017, 05:15:00, P15
     04/04/2017, 04:52:00, P15
     05/04/2017, 03:00:00, P15
     06/04/2017, 04:29:00, P15
     07/04/2017, 05:25:00, P15
];

Pause_description :
LOAD * INLINE [
     pause_code, pause_description
     PA, Pause Administrative
     PT, Pause toilette
     PD, Pause déjeuner
     P15, Pause 15min
];

Work :
LOAD * INLINE [
     Date, Work_duration
     03/04/2017, 158:25:00
     04/04/2017, 160:12:00
     05/04/2017, 159:12:00
     06/04/2017, 160:03:00
     07/04/2017, 159:32:00
];

Re: Calculation based on dimension selected

hariprasadqv — Tue, 18 Apr 2017 10:49:35 GMT

Hi Tsiry,

you can implement this scenario in two ways

1.Write three different expressions and Use conditional enabling

2.Use Pick

Thanks

Re: Calculation based on dimension selected

harson — Tue, 18 Apr 2017 10:58:27 GMT

hariprasad avula

Could you be more specific, or give an example please. I'm new to QlikView.

Re: Calculation based on dimension selected

hariprasadqv — Tue, 18 Apr 2017 11:11:27 GMT

Under expression tab of the chart/table you can find Conditional Check box.

Check the box and it will enable an expression field in that write following

getselectedvalue(pause_description)='Avec Pause Adminitrative'

and in the expression write the logic in expression field

[Work_duration]-[pause_duration]

the above expression will work on selection of 'Avec Pause Adminitrative'

use the same for the rest of the expressions

Re: Calculation based on dimension selected

harson — Tue, 18 Apr 2017 11:42:54 GMT

With this 4 dimensions, there are 13 possible combinations, so I have to create 13 different expressions and conditions.

Re: Calculation based on dimension selected

hariprasadqv — Tue, 18 Apr 2017 12:10:21 GMT

Not exactly.

If am understanding right, you have to write only 3 conditions

In the 1st expression condition use GetFieldSelections(pause_description)='Avec Pause Adminitrative',getselectedvalue(pause_description)='Avec Pause Adminitrative'

In the 2nd expression you can enable condition with implement with GetFieldSelections() and Subfield() function

and the 3rd use getselectedcount(pause_description)

(If nothing is selected getselectedcount() will return '0'. In Qlikview '0' is false other than zero is true. But ~0=-1)

Re: Calculation based on dimension selected

harson — Tue, 18 Apr 2017 12:27:13 GMT

Thank you for your reply,

But, maybe I have wrongly expressed my problem

I'll try to explain it again.

I have to automate the result of my DT according to the selected dimensions

With DT = [Work_duration] - Sum[pause_duration]

If "Avec Pause Administrative", "Avec Pause toilette" is cheked, DT=[Work_duration] - Sum({<pause_code={'PA','PT'}>}[pause_duration])

OR If "Avec Pause Administrative", "Avec Pause toilette",'Avec Pause déjeuner' is cheked, DT=[Work_duration] - Sum({<pause_code={'PA','PT','PD'}>}[pause_duration])

OR If "Avec Pause Administrative" is cheked, DT=[Work_duration] - Sum({<pause_code={'PA'}>}[pause_duration])

And so on...

Re: Calculation based on dimension selected

vinieme12 — Tue, 18 Apr 2017 12:27:31 GMT

Is this what you're after? check the app

Re: Calculation based on dimension selected

parul_mehta — Tue, 18 Apr 2017 12:29:36 GMT

You may want to try this

Re: Calculation based on dimension selected

parul_mehta — Tue, 18 Apr 2017 12:38:00 GMT

Also is your selection only dependent on Pause_description?

Or Date as well ? and any other field also ?

Re: Calculation based on dimension selected

harson — Tue, 18 Apr 2017 12:50:16 GMT

Date and Pause_description

Re: Calculation based on dimension selected

vinieme12 — Tue, 18 Apr 2017 12:58:31 GMT

did you check the app i posted?

Re: Calculation based on dimension selected

harson — Tue, 18 Apr 2017 13:03:23 GMT

Thanks Vineeth Pujari

It worked.