Count IF -on a condition

[cognos05]

Hi,

I wanted to count the number of accounts nested under a sales rep for those whose measure value is negative value for one of the columns.
                            YTD      PYTD    Account count for YTD with negative value
Sales rep Acct1    1            45          1
                Acct2    34          -36         1
                Acct3    -456     -456         1

So in the above example, I should get the count as 1 if the column is YTD with respect to sales rep
Below expression was used to count the number of accounts under each rep
count([Measure] within set siblings(currentMember([Your Rep and Account hierarchy])))

But now I should use a condition on my count, like if the value in the column YTD is negative then count the accounts under the rep.

I dont see a countif function i cognos,is there a way to achieve this.
Thanks,
Nithya

[MFGF]

I would use a filter() function around the set of siblings you are counting - the filter will return only those members whose measure value is negative. Would this work for you?

MF.


Sent from my iPad using Tapatalk HD

[cognos05]

Yes, it worked perfectly. Why dint I think of it :(.

Thanks MFGF you are awesome!!

[cognos05]

Hi MFGF,

I am trying to use the same functions in cognos 8.1 and cognos 10.2.1 .

It is working perfectly in Cognos 10 , but in 8 I add a query calculation or a data item  and add this item in the column and used filter() function around the set of siblings that I am counting, I dont get any error, but my Query item doesnt show up in the column.I am not sure why is it so, it is the same code as in cognos 10.

Thanks,
Nithya

[MFGF]

Hi MFGF,

I am trying to use the same functions in cognos 8.1 and cognos 10.2.1 .

It is working perfectly in Cognos 10 , but in 8 I add a query calculation or a data item  and add this item in the column and used filter() function around the set of siblings that I am counting, I dont get any error, but my Query item doesnt show up in the column.I am not sure why is it so, it is the same code as in cognos 10.

Thanks,
Nithya

What precise version of 8?

MF.

[cognos05]

Version 8.1

[MFGF]

Version 8.1

I think your problem is right there. 8.1 (and even 8.2) had some, um, let's call it quirky behaviour using dimensional expressions. When 8.3 was released and things got tightened up, lots of reports stopped working. The truth was they never should have worked but 8.1 was looser in it's interpretation of MDX than it perhaps could have been.

MF.