Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
Not applicable

## Limit a Dimension to a specific Date in a Table

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)

1 Solution

Accepted Solutions
MVP

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

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.

13 Replies
MVP

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

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.

Not applicable
Author

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.

MVP

May be this:

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

Not applicable
Author

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?

MVP

Can you share a screenshot of what you are seeing?

Not applicable
Author

Is there a way to share it with you privately?

Not applicable
Author

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)

Not applicable
Author

small error about (both should say IncidentCost)

MVP

Can you try this:

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

Tags
Community Browser