Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
|
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
Hi,
another solution to avoid the date formatting problem might be:
Sum({$<assets.day={"=assets.day=Max(total assets.day)"}>} Value)
hope this helps
regards
Marco
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)
Dev is probably right. You need to convert to a date.
Otherwise you could try
Sum ({< assets.day = {$ (= maxstring (assets.day))}>} Value)
Hi,
another solution to avoid the date formatting problem might be:
Sum({$<assets.day={"=assets.day=Max(total assets.day)"}>} Value)
hope this helps
regards
Marco
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.
Glad it worked for you.
Maybe these links are helpful:
Set modifiers with advanced searches ‒ QlikView
Set analysis and set expressions ‒ QlikView
Why is it called Set Analysis?
regards
Marco
Thanks Marco.
You saved my day haha !