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: 
Anonymous
Not applicable

Only show values of the last date

 

 

Hello everybody,

 

 

I have a quick question for you.

 

 

I want to create the following measure in my bar chart.

 

The sum of the column 'Value', but only the last uploaded values.

 

 

Background: new values are added to the data table at regular intervals, the old ones remain.

 

For each load, the date of the load time is written for each new line. As an an example:

 

 

 

Material

Value

  • assets.day

ABC

2000

2017-01-01

ABC

2500

2017-02-01

ABC

3000

2017-03-01

ABC

2750

2017-04-01

 

 

The measure should recognize that I only need the last (or in other words, the maximum) value for the column 'day'.

 

My formula for this:

 

 

Sum ({< assets.day = {$ (= max (assets.day))}>} Value)

 

 

Although I do not see a syntax error, all values remain at 0 in the chart.

 

 

Can anybody help me further?

 

Many Thanks

 

Chris

 

1 Solution

Accepted Solutions
MarcoWedel

Hi,

another solution to avoid the date formatting problem might be:

Sum({$<assets.day={"=assets.day=Max(total assets.day)"}>} Value)

QlikCommunity_Thread_280183_Pic1.JPG

hope this helps

regards

Marco

View solution in original post

6 Replies
devarasu07
Master II
Master II

Hi,

Try like this


Fact:

LOAD Material,

     Value,

     Date(assets.day) as Date

FROM

[https://community.qlik.com/thread/280183]

(html, codepage is 1252, embedded labels, table is @1);


Max Date value using set analysis expression

sum( {$<Date={"$(=max(Date))"}>}Value)

Capture.JPG

Anonymous
Not applicable
Author

Dev is probably right.  You need to convert to a date.

Otherwise you could try

Sum ({< assets.day = {$ (= maxstring (assets.day))}>} Value)

MarcoWedel

Hi,

another solution to avoid the date formatting problem might be:

Sum({$<assets.day={"=assets.day=Max(total assets.day)"}>} Value)

QlikCommunity_Thread_280183_Pic1.JPG

hope this helps

regards

Marco

Anonymous
Not applicable
Author

This one worked, thanks!

The dimension assets.day was already recognized as a date when I uploaded it from my database.

@ Marco: do you have a link to a document or another blog where I can try to understand your solution step by step? I would like to figure out how your formula works so I can use it in other charts as well.

Anonymous
Not applicable
Author

Thanks Marco.

You saved my day haha !