Filtered date not recognised as date

[mattphillip]

Hoping someone can help. I'm trying to produce a pivot table which is providing sub-total for applications based on the the final export date within a field called 'Date' which is filtered by an expression. The expression succeeds in pulling out the final export date per month for a particular session and is formatted as a date. However, the sub-totals are way higher than they should be which tells me that Qlikview doesn't recognise the filtered date as having values from the Date field. When I manually select a date within the Date field, the totals then look accurate. 

Date filter expression: =date(aggr(max({<Session={'2018/19'}>}Date),Month),'DD/MM/YYYY')

Sub-Total expression: =count([Applicant ID])

Does anyone have any idea what I might be doing wrong?

Many thanks,

Matt

[sunny_talwar]

In your current selections, are you seeing a selection in the Month field? or are you seeing selection in both month and date field?

[mattphillip]

Hi Sunny,

Thanks for the quick reply. I don't have a month field as such, rather the expression is filtering a populated field labelled as date to pick out the last export dates for each month (rather than the last calendar date). The format for the Date field is DD/MM/YYYY.

M

[sunny_talwar]

You don't have a month field?

But you have made a reference of your Month field here?

[mattphillip]

Sunny, my apologies. I misread your email (Trying to multi-task in the office isn't the easiest thing!). Yes, for the purposes of the expression I created a month field in the load script ( Month(Date)as Month,).

Yes, when I click on one of the filtered dates in the pivot table, it is making a selection within both the 'Date' and 'Month' fields.

[sunny_talwar]

Make sense, does it select a single date or is it selecting all dates within the month?

[mattphillip]

All the export dates within that month.

[sunny_talwar]

In that case, you might have to use a set analysis within your expression to display the Count related to the Max Date only.

[mattphillip]

Right I've tried this with the code below but I'm still getting very high subtotals...

=count({$<Date={"=MaxString(Date)"}>}[Applicant ID])

Any ideas?

[sunny_talwar]

Try something like this

=Count({$<Date = {"$(=MaxString(Date))"}>} [Applicant ID])