topic Re: Time period in QlikView

Time period

Wed, 14 May 2014 12:27:48 GMT

Hello everyone,

I am looking to create a gauge showing how many actions I have that are due by the end of the month and struggling with the expression. Looking for an expression that would have :

if([Target date]>Today() and [Target date]<Monthend, count(Actions))

Not quite sure how to formulate it.

Thank you

Anna

Re: Time period

alexandros17 — Wed, 14 May 2014 12:29:44 GMT

Count(if([Target date]>Today() and [Target date]<Monthend,Actions,0)

let me know

Re: Time period

its_anandrjs — Wed, 14 May 2014 12:36:11 GMT

Try with

Count({<[Target date] = { '>$(=Today( )), [Target date] = {'<$(=Monthend)'} >} Actions)

Re: Time period

Anonymous — Wed, 14 May 2014 12:56:31 GMT

Hi Neshina,

Try like this

=Count(if(date([Targetdate],'DD/MM/YYYY')>=Date(Today(),'DD/MM/YYYY') and Date([Targetdate],'DD/MM/YYYY')<=Date(MonthEnd,'DD/MM/YYYY'),Actions))

Re: Time period

Anonymous — Wed, 14 May 2014 13:03:11 GMT

Alessandro Saccone,

I bet it is just a mistyping, having 0 and ')' on the same key... And I usually utilize distinct with count:

Count(distinct if([Target date]>Today() and [Target date]<=Monthend, Actions))

Re: Time period

alexandros17 — Wed, 14 May 2014 13:07:12 GMT

The mistake is the missing parenthesis,

Count

(

if(

[Target date]>Today() and [Target date]<Monthend

,Actions

)

This expression count "Actions" when condition is true otherwise count 0, if you use Distinct and you have the same Action I don't know if it is correct to Discharge

Re: Time period

its_anandrjs — Wed, 14 May 2014 13:11:20 GMT

Hi Anna,

You can try this also

Count ( {<[Target date] = {">$(=Today()) <$(=Monthend(Today()))"} >} Actions )

Count({<[Target date] = { '>$(=Today( )) <$(=Monthend)'} >} Actions)

Re: Time period

Anonymous — Wed, 14 May 2014 13:12:13 GMT

Alessandro,

It is not sum(), it is count()... The 0-value is a value too, and will be counted. Just try this in a text box:
=count(0)

Re: Time period

alexandros17 — Wed, 14 May 2014 13:24:43 GMT

You're right ...

Re: Time period

Anonymous — Wed, 14 May 2014 13:30:28 GMT

So, we agree it was a mistyping

Re: Time period

Wed, 14 May 2014 13:32:20 GMT

Thank you all!