Adding AND Function

[#1]

Hello,

hope someone can help me with my problem.

First of all i wanted to count all my tasks which are longer in process then the desired duedate.

I created this code and it worked

=((Count(if(duedate<= Today(),duedate)))) - (Count({<[Status (gen)]={'done'}>}[Status (gen)]))

So it counts all tasks with an duedate shorter then the actual date, excluding all tasks that are already done.

[Status (gen)] can be "done" or "in progress"

Now i want to add an AND Function:

For example: AND [Category] = {' Supply Chain'}

i tried to add my AND but the code did not work

[Anonymous]

Hi Andreas,

Use :

+ : union of sets

*  : intersection of sets

Depending on you requirements.

Regards

Neetha

[anbu1984]

=((Count(if(duedate<= Today(),duedate)))) - (Count({<[Status (gen)]={'done'}>*<[Category] = {' Supply Chain'}>}[Status (gen)]))

[giacomom]

Hi Andreas,

you can add conditions in AND in your set analysis expression by adding them comma-separated inside the < > brackets like this:

=((Count(if(duedate<= Today(),duedate)))) - (Count({< [Category] = {' Supply Chain'} , [Status (gen)]={'done'}>}[Status (gen)]))

Regards,

Giacomo

[#5]

Ah ok!

So i can use the * or I add a , inside the >< brackets, got that

And how i can use the AND function on the first Count so it can be part of the condition?

Count(if(duedate<= Today(),duedate))))

[simenkg · Accepted solution]

Don't do this in set analysis. Do it in your script.

Count/Sum/Avg ( if()) is a performance killer and is something you should NEVER do.

In stead create a flag in your scipt like this:

load .

.

.

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

from

Table Tasks.qvd (qvd) ;

Then you use set analysis like this:

count({<OverdueFlag={1}>}Task)

Regards

SKG

[#7]

Thank you.

I tried it but there is an error at the comma after 'Supply Chain'

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

LOAD

.

.

.

`Category`;

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

SQL SELECT *

FROM database.task;

[giacomom]

Hi,

you can add the condition in the first Count using the expression:

     =Count({$ <[Category] = {' Supply Chain'} , duedate={"<=$(#vToday)"}>} duedate)

where vToday is the variable you created before in your script.

In this case you can remove the if inside a Count/Sum/Avg calculation as Simen Kind Gulbrandsen suggests.

Regards,

Giacomo

[simenkg]

LOAD * ,

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag;

SQL SELECT *

FROM database.task;

[#10]

Thank you very much, it worked perfectly!

The only thing I wonder now is, can I use this out of my LOAD code?

load .

.

.

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

from

Table Tasks.qvd (qvd) ;

So lets say I have another LOAD code where I add something.

I cant use the if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

because I cant use an attribute which is not in my active LOAD code.

Just to know, what if my duedate comes from another LOAD code then my Category ?