Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
darren_dixon
New Contributor III

Straight Table / Aggr or Set Analysis help

Hi

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:

PSNDateDescription
188018631/12/2199Ignore
188018624/07/2015Yes
188018624/07/2015Yes
188018607/05/2017Yes
188018607/05/2017Yes

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

DatePSN
24/07/20151

I've got as far as:

Count(Distinct

IF([Description]<>'Ignore',

[Date],))

Thanks,

Darren

1 Solution

Accepted Solutions
effinty2112
Honored Contributor

Re: Straight Table / Aggr or Set Analysis help

Hi Darren,

Try this straight table:

Year Month Count Distinct PSN
5670
201701896
201702793
201703842
201704710
201705695
201706529
201707536
201708537
201709132

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
101/02/2017Ignore
108/03/2017Yes
102/04/2017Yes
104/04/2017Yes
106/04/2017Ignore
106/06/2017Yes

When PSN = 1 is selected the straight table returns:

Year Month Count Distinct PSN
1
2017021

which looks correct.

Cheers

Andrew

View solution in original post

7 Replies

Re: Straight Table / Aggr or Set Analysis help

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antoniotiman
Honored Contributor III

Re: Straight Table / Aggr or Set Analysis help

Hi Darren,

maybe this

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

Regards,

Antonio

effinty2112
Honored Contributor

Re: Straight Table / Aggr or Set Analysis help

Hi Darren,

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

PSN Min({$<Description -={'Ignore'}>}Date)
188018624/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)
188018624/07/20152

Cheers

Andrew

darren_dixon
New Contributor III

Re: Straight Table / Aggr or Set Analysis help

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:

MonthYearPSN (Distinct count of PSN)
0720156
08

2015

7

Hopefully that makes sense

effinty2112
Honored Contributor

Re: Straight Table / Aggr or Set Analysis help

Hi Darren,

Try this straight table:

Year Month Count Distinct PSN
5670
201701896
201702793
201703842
201704710
201705695
201706529
201707536
201708537
201709132

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
101/02/2017Ignore
108/03/2017Yes
102/04/2017Yes
104/04/2017Yes
106/04/2017Ignore
106/06/2017Yes

When PSN = 1 is selected the straight table returns:

Year Month Count Distinct PSN
1
2017021

which looks correct.

Cheers

Andrew

View solution in original post

darren_dixon
New Contributor III

Re: Straight Table / Aggr or Set Analysis help

Hi Andrew,

That works!

Thank you for your time. Greatly appreciated.

Thanks,

Darren

effinty2112
Honored Contributor

Re: Straight Table / Aggr or Set Analysis help

Your welcome Darren!