Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
May be this,
Count(Aggr(If(count(Task)=0,1) ,Name))
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
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))
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
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
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)