topic Re: Straight Table / Aggr or Set Analysis help in QlikView

Straight Table / Aggr or Set Analysis help

darren_dixon — Fri, 08 Sep 2017 14:16:12 GMT

Please could you help I want to find the minimum Date where Description is not equal to Ignore.

I think I need to use the aggr function or set analysis but I'm struggling to make this work.

Table:

PSN	Date	Description
1880186	31/12/2199	Ignore
1880186	24/07/2015	Yes
1880186	24/07/2015	Yes
1880186	07/05/2017	Yes
1880186	07/05/2017	Yes

I'd like the straight table to look like this:

Date	PSN
24/07/2015	1

I've got as far as:

Count(Distinct

IF([Description]<>'Ignore',

[Date],))

Thanks,

Darren

Re: Straight Table / Aggr or Set Analysis help

Anil_Babu_Samineni — Fri, 08 Sep 2017 14:29:51 GMT

May be this?

Count({<Description -= {'Ignore'}, Date = {'$(=Min(Date))'}>} DISTINCT Date)

Re: Straight Table / Aggr or Set Analysis help

antoniotiman — Fri, 08 Sep 2017 14:38:30 GMT

Hi Darren,

maybe this

MinString({<Description-={'Ignore'}>} Date)

Regards,

Antonio

Re: Straight Table / Aggr or Set Analysis help

effinty2112 — Fri, 08 Sep 2017 14:45:05 GMT

Hi Darren,

This table will give the date you're looking for for each PSN.

PSN	Min({$<Description -={'Ignore'}>}Date)
1880186	24/07/2015

In your desired table what is the purpose of the PSN column?

In the expression you wrote you seem to be looking for the number of distinct dates where Description <> 'Ignore'. But the answer here is two (the 7th and 24th of July) not one.

PSN	Min({$<Description -={'Ignore'}>}Date)	Count({$<Description -={'Ignore'}>}DISTINCT Date)
1880186	24/07/2015	2

Cheers

Andrew

Re: Straight Table / Aggr or Set Analysis help

darren_dixon — Fri, 08 Sep 2017 14:59:32 GMT

The PSN is a unique number for a property.

I'm looking to find the minimum date from the date field per PSN where the description is not Ignore so that I can make a straight table to look like below:

Month	Year	PSN (Distinct count of PSN)
07	2015	6
08	2015	7

Hopefully that makes sense

Re: Straight Table / Aggr or Set Analysis help

effinty2112 — Sun, 10 Sep 2017 14:26:52 GMT

Hi Darren,

Try this straight table:

Year	Month	Count Distinct PSN
		5670
2017	01	896
2017	02	793
2017	03	842
2017	04	710
2017	05	695
2017	06	529
2017	07	536
2017	08	537
2017	09	132

I wrote a script that made many PSNs to analyse.

Year is this calculated dimension:

=Year(Aggr(Min({$<Description = {'Ignore'}>}Date),PSN))

Month is:

=Num(Month(Aggr(Min({$<Description = {'Ignore'}>}Date),PSN)),'00'

Count Distinct PSN is the expression:

=Count({$<Description = {'Ignore'}>}DISTINCT PSN)

The first PSN created by the script was:

PSN	Date	Description
1	01/02/2017	Ignore
1	08/03/2017	Yes
1	02/04/2017	Yes
1	04/04/2017	Yes
1	06/04/2017	Ignore
1	06/06/2017	Yes

When PSN = 1 is selected the straight table returns:

Year	Month	Count Distinct PSN
		1
2017	02	1

which looks correct.

Cheers

Andrew

Re: Straight Table / Aggr or Set Analysis help

darren_dixon — Tue, 12 Sep 2017 10:31:10 GMT

Hi Andrew,

That works!

Thank you for your time. Greatly appreciated.

Thanks,

Darren

Re: Straight Table / Aggr or Set Analysis help

effinty2112 — Tue, 12 Sep 2017 10:32:48 GMT

Your welcome Darren!