Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
darren_dixon
Contributor III
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
Master
Master

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
Anil_Babu_Samineni

May be this?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Master III
Master III

Hi Darren,

maybe this

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

Regards,

Antonio

effinty2112
Master
Master

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
Contributor III
Contributor III
Author

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

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

darren_dixon
Contributor III
Contributor III
Author

Hi Andrew,

That works!

Thank you for your time. Greatly appreciated.

Thanks,

Darren

effinty2112
Master
Master

Your welcome Darren!