Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am struggling with a simple issue.
A table has a date as dimension and a value as expression. I would like to add an expression showing this value for the "day-7".
Trying this is not working:
sum({<date={"$(=date(max(date)-7))"}>}value)
Using "above" function is not the right solution as it will not work for the 6 first rows of my table.
Attached is an example. Does someone have the solution to this?
Thanks in advance
Within set analysis must not only the values are matching else the formats must be the same. Date() without format-parameters will return the default date-format. If this is different from the format in field date it didn't match. Often it's helpful to use only an additionally numeric date-field for calculations.
- Marcus
Hello,
Yes, thanks in my application model I have this numeric date field.
But it's not the problem here.
Thanks
When you use $() you refer to a variable, and variables can to store only one value.
In this case, I recomend you use PEEK and make a pre-calculated field on script. Unless you need a interactive chart.
Doing it in the script is not possible I guess. I need an interactive chart indeed.
I think the suggestion from Fábio Simões would work with an interactive approach if you created in extra value-field which is associated to your date-field.
Instead of above() have you tried below() ?
Below(sum(value), 7)
Alternatively you could play with ugly string-functions like this:
subfield(concat({1} total value, ',', date), ',', rowno() + 7)
- Marcus
Thank you both for your help.
Below (like above) would not work as it works only with the dates displayed in the chart and would not work with the 6 first dates ( eg: first row is Jan 1st, I want to compare it with the figure 7 days ago, Dec 25th, which is not in the table).
I think this is the same result with your second expression.
The second expression includes a {1} total and would be independent from selections. This means if you select only the first 10 dates you will have no empty rows. But is it possible that in your real data-model this approach may not work or would be too complex.
- Marcus
And what about using the BELOW-function?
HTH Peter
Are you tried Above like that:
Above(Sum({<date =>} value),7)