Aggregate and count function

[environmental_leaf]

Hello,

A sample of my data is below and attached. Each facility, identified using a [Facility Code] reports a [Value] for each [Pollutant]. Facilities are categorized into industries using [PSC code].

I am looking to calculate the skew of the [Value] for each [PARAMETER_DESC] within a [PSC code] and then count the number of [Pollutants] within a [PSC code] with a positive skew. Qlik has an in-built skew function, but I can't seem to perform both steps at once! 

The final visualization will be TABLE that lists [PSC code] and the number of pollutants with a positive skew. If I could graph this - potentially a bar graph that shows PSC code on the x-axis and number of pollutants with a positive skew on the y axis, that would be great too! 

Any help would be great! Thank you!! 

PSC code	Facility Code	Pollutant	Value
435	AR0052051	Chromium, trivalent total recoverable	0.8601
450	WV0004499	Benzo[b]fluoranthene	0.0069
435	WV1015848	Manganese, total [as Mn]	2
435	WV1005481	Aluminum, total [as Al]	0.43
434	WV1023951	Aluminum, total [as Al]	0.14
450	UTR000601	Aluminum, total [as Al]	0.9
435	CTU005023	Chromium, trivalent total recoverable	0.4
420	MDG914106	Chromium, trivalent total recoverable	0.786363
420	LAL036412	Benzo[b]fluoranthene	0.654

[JustinDallas]

Hmm, I tried to solve the problem, by using a truncated and cleaned Inline statement:

DATA:
LOAD * Inline
[
'PSC code','Facility Code','Pollutant','Value'
'435','AR0052051','Chromium, trivalent total recoverable','0.86010004',
'434','WV1015848','Manganese, total','2',
'434','WV1005481','Aluminum, total','0.43',
'434','WV1023951','Aluminum, total','0.14',
'435','KYR10I122','Chromium, trivalent total recoverable','0.3',
'420','LAL036412','Benzo','0.654',
'434','MAU000097','Chromium, trivalent total recoverable','0.235',
'434','PRU201920','Manganese, total','0.985',
'434','UTR000146','Aluminum, total','0.21',
'450','UTR000601','Aluminum, total','0.9',
'435','CTU005023','Chromium, trivalent total recoverable','0.4',
'420','CTU005024','Benzofluoranthene','0.2',
'450','UTR000668','Chromium, trivalent total recoverable','0.9',
]
;

EXIT Script
;

But the documentation on Skew is kind of weird, they don't even use any of the higher forms involving Set Analysis.  I thought AGGR would have to be thrown in the mix, "Aggr(Skew( TOTAL <[PSC code]> Value),[PSC code], [Pollutant])" but all that did was throw out a garbage value.

[environmental_leaf]

Wow, thanks for taking the time to figure this out!

Yes, the documentation is very minimal! Did you write the inline script manually? The real data is thousands of records long so that may be too laborious.

[JustinDallas]

No, I opened it up in Excel, and then used this to create the rows for the script:

=CONCATENATE("'",A1,"','",B1,"','",C1,"','",D1,"',")

Then you just copy the values into an Inline load and voila.  Sorry I couldn't figure out the solution to your problem.

[MynhardtBurger]

See the attached.

Ended up with the following measure:

=Count(
	aggr(
		if(skew(Value) > 0, skew(Value), null()),
	    [PSC code],Pollutant
	)
)