Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jumiprado
New Contributor III

Count number of days with two dates (From - To)

Hey guys!

I need to take the number of days between 2 dates but per month for example

FROM: 15/03/2018

To: 30/04/2018

i need to receive

MonthDays
March15
April30

and a total = 45 days

THANKS!

Tags (1)
7 Replies

Re: Count number of days with two dates (From - To)

You need this in a table with Month as a dimension?

jumiprado
New Contributor III

Re: Count number of days with two dates (From - To)

i need to create a field to measur the presentism days, today i have a field that returns 30 days for all of the cases, thats wrong about cases like i posted!

Thanks for your question!

Re: Count number of days with two dates (From - To)

Did you just try To - From + 1 as days

jumiprado
New Contributor III

Re: Count number of days with two dates (From - To)

yes, but i have this

Per each mont-year of salary settlement i need to count the number of days because the system give me 30 days in all cases and i need count per month-year the number of days

i was clear? Thanks a lot!!

Re: Count number of days with two dates (From - To)

Not entirely sure I understand

jumiprado
New Contributor III

Re: Count number of days with two dates (From - To)

i have this expression but not works very well, in some cases i have error

if(left( BASE_Cod_Cambio,1) = 'C',day(f_BASE_Fecha_Desde),

if( f_BASE_Fecha_Desde > FechaAfect AND f_BASE_Fecha_Hasta < MonthEnd( FechaAfect) , DAY(f_BASE_Fecha_Hasta)-DAY(f_BASE_Fecha_Desde)+1,

if( f_BASE_Fecha_Hasta < MonthEnd( FechaAfect), DAY(f_BASE_Fecha_Hasta),

IF(f_BASE_Toma_Posesion < FechaAfect or isnull(f_BASE_Toma_Posesion) and f_BASE_Fecha_Desde < FechaAfect,  '30',

      IF(f_BASE_Toma_Posesion> FechaAfect, 32- DAY(f_BASE_Toma_Posesion))  ))))

And i have other problem to put this on script, because "FechaAfect" is in other table in my model!!

Re: Count number of days with two dates (From - To)

Hi,

maybe one solution could be:

QlikCommunity_Thread_293471_Pic1.JPG

table1:

LOAD *, RangeMin(To,Floor(MonthEnd(Month)))-RangeMax(From,Month)+1 as Days;

LOAD *, MonthName(From,IterNo()-1) as Month

INLINE [

    From, To

    15/03/2018, 30/04/2018

    10/03/2018, 31/03/2018

    30/04/2018, 01/05/2018

    01/03/2018, 01/06/2018

    30/11/2018, 03/02/2019

] While MonthName(From,IterNo()-1)<=To;

see also:

Number of days per month

hope this helps

regards

Marco

Community Browser