Set Analysis - Date Range

[ivandrago]

Hi,

I have the attached QlikView Document, there are two tables one table will have All the results the other table is using a set Analysis to exclude results, below is what I want it to exclude...

I do not include the following egDates to be included on the result sets:

01/01/1930 00:00:00
10/01/1930 00:00:00
01/10/1930 00:00:00
01/01/2030 00:00:00

And I also want to exclude EgDates where the EgDate is Greater than the End of the Current Month we are in i.e. 31-Aug-2017

The current Set Analysis does not seem to be working as the egDate is including 01/01/1930 00:00:00 for one of the ID's, so not sure what is going on there?

Thanks

[sunny_talwar · Accepted solution]

Try this

sum({$<EgDate = {"=Not Match(Year(EgDate), 1930, 2030)"}*{"$(='<=' & TimeStamp(Floor(MonthEnd(Today())), 'DD/MM/YYYY hh:mm:ss'))"}>}aggr(sum(DISTINCT {$<EgDate = {"=Not Match(Year(EgDate), 1930, 2030)"}*{"$(='<=' & TimeStamp(Floor(MonthEnd(Today())), 'DD/MM/YYYY hh:mm:ss'))"}>} CredLim), CustNo))

and

sum({$<EgDate = {"=Not Match(Year(EgDate), 1930, 2030)"}*{"$(='<=' & TimeStamp(Floor(MonthEnd(Today())), 'DD/MM/YYYY hh:mm:ss'))"}>}aggr(sum(DISTINCT {$<EgDate = {"=Not Match(Year(EgDate), 1930, 2030)"}*{"$(='<=' & TimeStamp(Floor(MonthEnd(Today())), 'DD/MM/YYYY hh:mm:ss'))"}>} Val), CustNo))

[ivandrago]

Thanks Sunny, I have another issue which I want to do but not sure how to do it, I will add another post.