Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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)

Thank you in advance for your help!

1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

13 Replies
sunny_talwar

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.

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.

sunny_talwar

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?

sunny_talwar

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)

sunny_talwar

Can you try this:

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