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

Table: Day - 7 figures issue

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

13 Replies
marcus_sommer

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

Not applicable
Author

Hello,


Yes, thanks in my application model I have this numeric date field.

But it's not the problem here.


Thanks

fsimoes81
Partner - Contributor III
Partner - Contributor III

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.

Not applicable
Author

Doing it in the script is not possible I guess. I need an interactive chart indeed.

marcus_sommer

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

Not applicable
Author

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.

marcus_sommer

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

prieper
Master II
Master II

And what about using the BELOW-function?

HTH Peter

fsimoes81
Partner - Contributor III
Partner - Contributor III

Are you tried Above like that:

Above(Sum({<date =>} value),7)