Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

count if with two dimensions

Hello,

I want to create a statistic box for my results of the pivot table below:

I have two dimensions: EnterpriseID and period

   

2017Quarter1Quarter 2
EnterpriseID (KDNR)Enterpriseaccounting profit (f_RE)accounting profit (f_RE)
110A-365.000,005.000,00
120B-250.000,00-50.000,00
130C-180.000,00-80.000,00
140D-35.000,0025.000,00

the result should be like this:

   

Year 2017Number of Enterprises with negative accounting profit
6
Quarter 14
Quarter 22

I used

=Count({<KDNR={"=$(f_RE)<0"}>} KDNR) but it only works in the following case with on quarter

Year 2017Number of Enterprises with negative accounting profit
4
Quarter 14

If I want to evaluate it for two or more quarters I only get:

Year 2017Number of Enterprises with negative accounting profit
4
Quarter 14
Quarter 24

What is wrong???

Thanks a lot!!!

1 Solution

Accepted Solutions
sunny_talwar

Create a new field in the script....

AutoNumber(KDNR&Quarter) as Key

and then try this

=Count({<Key={"=$(f_RE)<0"}>} KDNR)

View solution in original post

8 Replies
Sergey_Shuklin
Specialist
Specialist

Hello, Tanja!

If I understood correct Quarter1 and Quarter2 are values of "period" field?

If so you can just add one more condition to your expression:

=Count({<KDNR={"=$(f_RE)<0"}, period={'Quarter1'}>} KDNR)


Should work.

sunny_talwar

Create a new field in the script....

AutoNumber(KDNR&Quarter) as Key

and then try this

=Count({<Key={"=$(f_RE)<0"}>} KDNR)

Sergey_Shuklin
Specialist
Specialist

Attaching a screenshot:

count_neg.png

Anonymous
Not applicable
Author

Unfortunately doesn't work in my case! Don't know why...

Nevertheless thank you very much for your help and time!

Anonymous
Not applicable
Author

YES!!! That's it!!!

Thousand thanks Sunny!!!

You're great!!!

Anonymous
Not applicable
Author

stalwar1

Hello!

I still have Problems with the correct Syntax of set Analysis. Perhaps you can help again?

Now I want to add a second condition that filters the results where both conditions are true

Count all Enterprises where

{<Key={"=$(f_SchwellUebFilter)=1"}>} KDNR) AND

{<Key={"=$(f_RE)<0"}>} KDNR)

How does that work in set analysis?

I tried this, but doesn't work:

=Count({<Key={"=$(f_SchwellUebFilter)=1"+"=$(f_RE)<0"}>} BBNR)

sunny_talwar

Try like this

=Count({<Key={"=$(f_SchwellUebFilter)=1 and $(f_RE)<0"}>} BBNR)

Anonymous
Not applicable
Author

Thanks a lot!!! That works!!!