Conditional calculation on output of aggr(on temp table)

[#1]

Hi,

I have a of requirement in which "I want to count the records based on certain condition, which needs to be calculated on temp table( i.e, output of aggr )"

The base expression :-  aggr(count(Task) ,Name)

So it creates a virtual temporary table inside it with two columns with some name say, Count(Task) and Name

Now I want the count of those names for which the count(Task) is zero.

How can I write expression for this ?

Please help me over the same.

Thanks and regards,

Anshul Sahu

[tamilarasu · Accepted solution]

Hi,

May be this,

Count(Aggr(If(count(Task)=0,1) ,Name))

[#3]

Hi Pravesh,

Thanks for the reply,

But its not giving me correct output, actually I want to apply the condition on the output of the temp table(aggr function),

i.e, I want the count of those 'Name' for which count(Task) is zero in Temp table.

Regards,

Anshul Sahu

[ciaran_mcgowan]

Hi Anshul,

Correct me if I'm wrong but won't Aggr(Count(Task), Name) always return a 1 for each value, regardless of the value of the task?

Would set analysis be an option or am I missing some complexity?

     Aggr(Count({<Task={0}>}Task) ,Name)

That will work for a chart and if you want to return the value in a text box, you can just add Sum().

     Sum(Aggr(Count({<Task={0}>}Task) ,Name))

[#5]

Hi Ciaran,

Thanks for such a warm reply !

Actually Aggr(Count(Task), Name)  won`t return 1 always, The report which I am building is on Manager level grain and inside Manager many Name can be there, so my requirement is that in one column i want, under a manager how many names are there which don`t have any task or count(task) is zero. Task is a descriptive field in one of my table in my data model.

Thanks and regards,

Anshul Sahu

[#6]

Hi Tamil,

Its working completely fine !!!! Thanks a lot   .

Can you also please help me out in understanding this expression ? How we applied conditional logic inside aggr itself ? means we did calculation on that temp table before it actually got formed ! and what does ,1 specifies over here ?

Again Thanks and warm regards,

Anshul Sahu

[tamilarasu]

Hi Anshul,

Sorry for the late reply.I used "IF" condition inside the aggr function. This will be applied on the virtual table. Finally, we are counting all the 1's.

If(count(Task)=0,1)