Limit a Dimension to a specific Date in a Table

[#1]

I have a table in Qlik Sense with 3-columns: two dimensions, and one measure: metricDate, AccountCode, and Count(Tickets)

However I want my table to only show records for a single date (there will be many rows for this date). I get the right date like this:

Max({$<IncidentCost-={0}>}Date(metricDate),'YYYY-MM-DD')

(Max Date where the Incident Cost value is not 0)

The calculation above works perfectly in a KPI - but not as a 'dimension limitation; for my dimension metricDate.

Is there any way I can use something like this to force the table to only return records for that date that works in the KPI? I'm assuming I need an expression for the "dimension limitation' but I'm not sure. (Due to requirements we don't want the user to pick this date themselves from a filter box - unless we can default the filter box to that date).

FYI: the KPI correctly returns 2016-02-14 (but it will change often)

Thank you in advance for your help!

[sunny_talwar · Accepted solution]

You can take two approaches here:

1) Calculated Dimension

Aggr(Only({<IncidentCost -= {0}>} metricDate), metricDate) and then select 'Suppress When Value is Null' on the dimension tab

2) Add Set Analysis to all your expressions

If you have an expressions like Sum(Sales), change it to Sum({<IncidentCost -= {0}>} Sales)

I would recommend using 2nd method unless you don't want to change your expressions for some other reason.

[#3]

YOU are awesome, but this is very hard. I used the Aggr(Only({<IncidentCost -= {0}>} metricDate), metricDate) and now my table is correct.....but I assumed the bar chart would therefore also reflect that change but the date is not 'selected' if you know what I mean. Is there a way to select the Date as well since the bar chart doesn't actually show dates itself (so I can't use that expression).

FYI: I could put a date filter on the page - but there are so many dates I don't want the user to have to figure out which one - this is why I was trying to get the table down to showing only one date.

[sunny_talwar]

May be this:

Aggr(Only({<IncidentCost -= {0}, metricDate = {"$(=Date(Max({<IncidentCost -= {0}>}metricDate), 'YYYY-MM-DD'))"}>} metricDate), metricDate)

[#5]

Something strange is preventing the second reference to the measure from being understood as a measure in the expression. What I mean is that the first "IncidentCost" is brown indicating it is recognized as a true available field, but the second one you nested in the date format is staying black. Any ideas why that might be?

[sunny_talwar]

Can you share a screenshot of what you are seeing?

[#7]

Is there a way to share it with you privately?

[#8]

Since I have to be careful sharing screen shots (under NDA), I will explain that I had used this expression to get the last ticket date where the incident cost was not 0.

Max({$<Incident-={0}>}ticketDate)

I would like to use this expression (somehow) to filter the measure on my bar chart - now shown as Sum(IncidentCost)

[#9]

small error about (both should say IncidentCost)

[sunny_talwar]

Can you try this:

Sum({<ticketDate = {"$(=Date(Max({<IncidentCost -= {0}>} ticketDate), 'dateFormatforTicketDateHere'))"}>} IncidentCost)