topic Tricky aggregation question in QlikView

Tricky aggregation question

Wed, 25 Nov 2020 16:16:04 GMT

Hello QV Community -

I'm stuck on this question: I have a table with four columns: Date, Time, Market and Station. I want to display a text box showing the count of rows where there is just 1 station when grouped by Date&Time&Market.

For example, if my Date, Time, Market, Station data is this ...

Date	Time	Market	Station
2015-03-01	1000	Dallas	WFAA
2015-03-02	1000	Dallas	WUSA
2015-03-03	1100	Dallas	WFAA
2015-03-03	1100	Chicago	WBBM
2015-03-03	1100	Chicago	WMAQ
2015-03-03	1100	Chicago	WLS
2015-03-03	0900	Portland	KYW

... the expected count would be 2 (the bold rows).

Thank you.

Re: Tricky aggregation question

Thu, 24 Mar 2016 18:41:53 GMT

Try Like Below:

Sum(IF(Aggr(Count(Distinct Station),Date,Time,Market) = 1 , 1, 0))

Re: Tricky aggregation question

swuehl — Thu, 24 Mar 2016 18:48:48 GMT

Not sure I understand your analysis, what about rows 1 and 2?

Re: Tricky aggregation question

Thu, 24 Mar 2016 18:50:44 GMT

As per your description, you should get 4 Rows and those are 1,2,3,7

Re: Tricky aggregation question

MarcoWedel — Fri, 25 Mar 2016 02:33:27 GMT

-Sum(Aggr(Count(Distinct Station)=1,Date,Time,Market))

hope this helps

regards

Marco