topic Re: count if with two dimensions in QlikView

count if with two dimensions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: count if with two dimensions

Sergey_Shuklin — Thu, 25 Jan 2018 16:41:59 GMT

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.

Re: count if with two dimensions

sunny_talwar — Thu, 25 Jan 2018 16:48:52 GMT

Create a new field in the script....

AutoNumber(KDNR&Quarter) as Key

and then try this

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

Re: count if with two dimensions

Sergey_Shuklin — Thu, 25 Jan 2018 16:55:28 GMT

Attaching a screenshot:

Re: count if with two dimensions

Anonymous — Fri, 26 Jan 2018 12:44:51 GMT

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

Nevertheless thank you very much for your help and time!

Re: count if with two dimensions

Anonymous — Fri, 26 Jan 2018 12:45:58 GMT

YES!!! That's it!!!

Thousand thanks Sunny!!!

You're great!!!

Re: count if with two dimensions

Anonymous — Wed, 31 Jan 2018 13:30:24 GMT

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)

Re: count if with two dimensions

sunny_talwar — Wed, 31 Jan 2018 13:35:08 GMT

Try like this

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

Re: count if with two dimensions

Anonymous — Thu, 01 Feb 2018 08:03:26 GMT

Thanks a lot!!! That works!!!