Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chschulz
New Contributor II

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

Re: Only show values of the last date

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

6 Replies
devarasu07
Honored Contributor II

Re: Only show values of the last date

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

atkinsow
Valued Contributor II

Re: Only show values of the last date

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

Otherwise you could try

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

Re: Only show values of the last date

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

chschulz
New Contributor II

Re: Only show values of the last date

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.

chschulz
New Contributor II

Re: Only show values of the last date

Thanks Marco.

You saved my day haha !

Community Browser