Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Conditional calculation on output of aggr(on temp table)

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

1 Solution

Accepted Solutions

Re: Conditional calculation on output of aggr(on temp table)

Hi,

May be this,

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

View solution in original post

6 Replies

Re: Conditional calculation on output of aggr(on temp table)

Hi,

May be this,

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

View solution in original post

Not applicable

Re: Conditional calculation on output of aggr(on temp table)

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

Partner
Partner

Re: Conditional calculation on output of aggr(on temp table)

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))

Not applicable

Re: Conditional calculation on output of aggr(on temp table)

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

Not applicable

Re: Conditional calculation on output of aggr(on temp table)

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

Re: Conditional calculation on output of aggr(on temp table)

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)