Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
shilina_klara
Contributor III
Contributor III

check the date in the period

Help, please, beginner!)Sorry for my English

I have a list of employees with their absence in the form:

BeginningExpiration
Employee 125.04.201725.04.2017
Employee 217.07.201730.07.2017
Employee 301.01.201531.12.2017
Employee 407.10.201608.01.2017
Employee 111.05.201717.05.2017

and there is a reference book with the beginning dates of the week and the names assigned to them, which are used as a sample, for example:

04/17/2017 W16

04/24/2017 W17

05/01/2017 W18

05/05/2017 W19

05/15/2017 W20

and by the choice of the name of the week (W17 for example), I need to count people in the graphs by the type of absence and by the months that this week is included in their period (Beginning-Expiration).

I would be very grateful!

11 Replies
kaushiknsolanki

Hi,

What you need is Intervalmatch() function.

See below helpful links.

QlikView IntervalMatch

IntervalMatch Function in QlikView

Regards,

Kaushik solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
MarcoARaymundo
Creator III
Creator III

Try:

CALENDAR:

LOAD * INLINE [

     WeekKey,StartDate,WeekID

     16,17/04/2017,W16

     17,24/04/2017,W17

     18,01/05/2017,W18

     19,05/05/2017,W19

     20,15/05/2017,W20

];

LOGIN:

LOAD

     Week(Date(Date#(Begin, 'DD/MM/YYYY')))                         as WeekKey,

     Employee,

     Date(Date#(Begin, 'DD/MM/YYYY')) as Begin,

     Date(Date#(Expiration, 'DD/MM/YYYY')) as Expiration;

LOAD * INLINE [

     Employee,Begin,Expiration

     Employee1,25/04/2017,25/04/2017

     Employee2,17/04/2017,18/04/2017

     Employee3,01/05/2017,31/05/2017

     Employee4,05/05/2017,08/05/2017

     Employee1,15/05/2017,17/05/2017

];

table.png

expression -> If(Count({1<WeekID={'*'}>} WeekID) > 0,1,' ')

shilina_klara
Contributor III
Contributor III
Author

thechacal

Thank you very much for your prompt response.

In the pie chart, it always shows 100% and does not divide by vacation types

MarcoARaymundo
Creator III
Creator III

Duplicate table,  remove Employee dimension, change expression, chage chart type to pie.

pie.png

shilina_klara
Contributor III
Contributor III
Author

Hello!

Yes, all cool I got to build a diagram, thanks to you.

But there are big periods from 2016 to 2017 for one person and it is necessary to check whether the week chosen in the filter is included in this period, and so your formula counts all who start the period enters this week

shilina_klara
Contributor III
Contributor III
Author

Hello!

This is a wonderful formula, thank you very much.

But how to make it pull out not a week, but the code of the week?

MarcoARaymundo
Creator III
Creator III

Hi!

Try...

Change expression in table chart to: If(Count({<WeekID={'*'}>} WeekID) > 0,1,' ')

Change expression in pie chart to: Count(Employee)

shilina_klara
Contributor III
Contributor III
Author

Maybe I do not understand how it works, but here's the picture I see:

There is an employee 1 he has the beginning of 20.03.2017, the end of 06.08.2017, and he shows on the table only W12, and should have shown all these weeks:

20.03.2017 W12

27.03.2017 W13

03.04.2017 W14

10.04.2017 W15

17.04.2017 W16

24.04.2017 W17

01.05.2017 W18

08.05.2017 W19

15.05.2017 W20

22.05.2017 W21

29.05.2017 W22

05.06.2017 W23

12.06.2017 W24

19.06.2017 W25

26.06.2017 W26

03.07.2017 W27

10.07.2017 W28

17.07.2017 W29

24.07.2017 W30

31.07.2017 W31

MarcoARaymundo
Creator III
Creator III

Sorry!

I did not understand what you need.

Please share your app so I can review it.