count if with two dimensions

[Anonymous]

Hello,

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

I have two dimensions: EnterpriseID and period

	2017	Quarter1	Quarter 2
EnterpriseID (KDNR)	Enterprise	accounting profit (f_RE)	accounting profit (f_RE)
110	A	-365.000,00	5.000,00
120	B	-250.000,00	-50.000,00
130	C	-180.000,00	-80.000,00
140	D	-35.000,00	25.000,00

the result should be like this:

Year 2017	Number of Enterprises with negative accounting profit
	6
Quarter 1	4
Quarter 2	2

I used

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

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

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

Year 2017	Number of Enterprises with negative accounting profit
	4
Quarter 1	4
Quarter 2	4

What is wrong???

Thanks a lot!!!

[Sergey_Shuklin]

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 · Accepted solution]

Create a new field in the script....

AutoNumber(KDNR&Quarter) as Key

and then try this

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

[Sergey_Shuklin]

Attaching a screenshot:

[Anonymous]

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

Nevertheless thank you very much for your help and time!

[Anonymous]

YES!!! That's it!!!

Thousand thanks Sunny!!!

You're great!!!

[Anonymous]

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]

Thanks a lot!!! That works!!!