topic Re: sum a value based on finding the most recent date in QlikView

sum a value based on finding the most recent date

Mon, 22 Jul 2013 18:43:41 GMT

I have a stock value saved for a number of weeks (aligned in the table with the "week beginning" date). Every week a new value is added with a new date.

I am trying to produce an expression that will always pick the most recent weeks value to display...and this is what I have so far:

=Sum({$<Date={$(=LatestDate)}>}[Value (USD)])

where Latest Date is a variable representing the formula MaxString(Date).

I cannot get this to produce a result even though the variable on its own in a text box returns the latest date OK...

Can somebody please point me in the right direction.

Thanks......

Re: sum a value based on finding the most recent date

swuehl — Mon, 22 Jul 2013 18:57:43 GMT

Have you tried enclosing your date into single quotes:

=Sum({$<Date={'$(LatestDate)'}>}[Value (USD)])

Re: sum a value based on finding the most recent date

Mon, 22 Jul 2013 19:04:40 GMT

Thanks for the prompt reply:

Just gave it a go with no luck....

If I type the actual date between the 2 quotes, I get the correct values returned. It just seems to be the way the formula is interacting with the variable....

Re: sum a value based on finding the most recent date

Mon, 22 Jul 2013 19:04:48 GMT

Thanks for the prompt reply:

Just gave it a go with no luck....

If I type the actual date between the 2 quotes, I get the correct values returned. It just seems to be the way the formula is interacting with the variable....

Re: sum a value based on finding the most recent date

Mon, 22 Jul 2013 19:05:04 GMT

Thanks for the prompt reply:

Just gave it a go with no luck....

If I type the actual date between the 2 quotes, I get the correct values returned. It just seems to be the way the formula is interacting with the variable....

Re: sum a value based on finding the most recent date

Mon, 22 Jul 2013 19:06:32 GMT

Thanks for the prompt reply:

Just gave it a go with no luck....

If I type the actual date between the 2 quotes, I get the correct values returned. It just seems to be the way the formula is interacting with the variable....

Re: sum a value based on finding the most recent date

swuehl — Mon, 22 Jul 2013 19:08:06 GMT

If you remove the label from your chart expression, QV will show the expression itself, with dollar sign expansion being evaluated. What do you see?

Re: sum a value based on finding the most recent date

Mon, 22 Jul 2013 19:15:32 GMT

With my original expression I see “-“. With the variation you have suggested I see the value $0.....(which I guess means the formula syntax is correct, but is not returning the value I expect)....

Getting closer, but I am still stumped as to why the variable on its own returns the value 8/7/2013, if I input 8/7/2013 into the formula, I get the value I am expecting, but putting the 2 together gives me $0.....

Re: sum a value based on finding the most recent date

swuehl — Mon, 22 Jul 2013 20:14:41 GMT

Have you tried the variable expansion without the equal sign as shown above? And, how does your expression show up exactely in the label? I assume your variable 8/7/2013 is evaluated as numeric expression, so it will return a number near zero.

Re: sum a value based on finding the most recent date

Mon, 22 Jul 2013 21:02:15 GMT

Thanks for the continued help.....I'll try to cover as many bases here as possible:

If I enter the expression: =$(LatestDate) - I get 08/07/2013

If I enter: =Sum({$<Date={'08/07/2013'}>}[Value (USD)]) - I get $15,000 (ish)

If I enter: =Sum({$<Date={'$(LatestDate)'}>}[Value (USD)]) - I get $0

If I enter =Sum({$<Date={$(=LatestDate)}>}[Value (USD)]) - I get just - (a dash)

Re: sum a value based on finding the most recent date

Mon, 22 Jul 2013 22:08:01 GMT

Hi Mike

QlikView has a very handy little function for just your kind of issue. You have a list that grows each week with a new date and value. You want to display the most recent value in the list. Hence, your list will be in date order ascending by default with each 'week commencing' date having a different 'value' as in the table below

Week Commencing Value

21/07/2013 10

28/07/2013 15

04/08/2013 56

To display your latest value you can use the 'FirstSortedValue' function. Create a text box and type in:

=Firstsortedvalue(Value,-[Week Commencing])

You will see the value 56 appear in the text box when you reload the application.

If you add another line e.g. 11/08/2013 with a value of 76 then reload your application you will see 76 displayed in the text box.

The function works by taking the first value in a sorted list. The minus sign tells it to look from the end of the list first hence giving you the most recent entry.

Hope that helps.

Kind regards

Steve