Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: Table: Day - 7 figures issue

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

Re: Table: Day - 7 figures issue

Hello,


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

But it's not the problem here.


Thanks

fsimoes81
New Contributor III

Re: Table: Day - 7 figures issue

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

Re: Table: Day - 7 figures issue

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

Re: Table: Day - 7 figures issue

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

Re: Table: Day - 7 figures issue

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.

Re: Table: Day - 7 figures issue

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
Honored Contributor II

Re: Re: Table: Day - 7 figures issue

And what about using the BELOW-function?

HTH Peter

fsimoes81
New Contributor III

Re: Re: Table: Day - 7 figures issue

Are you tried Above like that:

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

Community Browser