CODEWITHSUNDEEP
dateexpressionmaxqlikview
Matt
Matt

Reputation: 15071

Qlikview expression select value that has the latest date for reference line

I have a basic bar graph and i want to add a reference line expresion to it.

However when i add the line it is taking the wrong value.

Sample data

SaleDate   DaysToPay
01/02/2015 60
01/03/2015 60
01/06/2015 60
01/07/2015 30

As you can see the days to pay drops to 30 for the most recent date, this needs to be the value the reference line uses.

Currently the expression I have tried i:

=[DaysToPay]

Which gives the value of 60.

In SQL I would do the following, but this doesn't work in a QV expression for a reference line.

SELECT s.*
FROM sales s
INNER JOIN
    (SELECT DaysToPay, MAX(SaleDate) AS MaxSaleDate
    FROM sales 
    GROUP BY DaysToPay) groupDays
ON s.DaysToPay = groupDays.DaysToPay
AND s.SaleDate = groupDays.MaxSaleDate

Upvotes: 0

Views: 6591

Answers (1)

bdiamante
bdiamante

Reputation: 17550

Assuming your data is loaded in as a date field, you can force Qlik to use the value associated with the latest date in your reference line with:

=concat({$<SaleDate={"$(=max(SaleDate))"}>} DaysToPay)

I use the concat function above, however in the situation where there is only ever one record per day, the function is irrelevant as you will only ever have one value to concat. In a situation where you have multiple values per day, you'll need to decide what logic you want to use to keep one of the values, or aggregate them somehow (i.e. average).

Upvotes: 1

Related Questions