Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mattphillip
Creator
Creator

Filtered date not recognised as date

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

Labels (4)
19 Replies
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
Creator
Creator
Author

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?

image.png

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

mattphillip
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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])