topic Re: Table: Day - 7 figures issue in QlikView

Table: Day - 7 figures issue

Tue, 02 Dec 2014 10:39:31 GMT

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

Re: Table: Day - 7 figures issue

marcus_sommer — Tue, 02 Dec 2014 12:38:27 GMT

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

Re: Table: Day - 7 figures issue

Tue, 02 Dec 2014 12:58:33 GMT

Hello,

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

But it's not the problem here.

Thanks

Re: Table: Day - 7 figures issue

fsimoes81 — Tue, 02 Dec 2014 13:42:02 GMT

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.

Re: Table: Day - 7 figures issue

Tue, 02 Dec 2014 13:51:08 GMT

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

Re: Table: Day - 7 figures issue

marcus_sommer — Tue, 02 Dec 2014 14:13:48 GMT

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

Re: Table: Day - 7 figures issue

Tue, 02 Dec 2014 14:22:55 GMT

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

marcus_sommer — Tue, 02 Dec 2014 14:30:20 GMT

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

Re: Re: Table: Day - 7 figures issue

prieper — Tue, 02 Dec 2014 16:23:44 GMT

And what about using the BELOW-function?

HTH Peter

Re: Re: Table: Day - 7 figures issue

fsimoes81 — Tue, 02 Dec 2014 16:38:11 GMT

Are you tried Above like that:

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

Re: Re: Table: Day - 7 figures issue

Thu, 04 Dec 2014 11:04:22 GMT

Fabio,

It's almost working, the problem is when you select a date, the table is still showing all values.

Re: Re: Table: Day - 7 figures issue

fsimoes81 — Thu, 04 Dec 2014 13:45:23 GMT

Ok, explain me please, why can't to create another field on script to bring the value you need.

Something like:

Peek(value,-7) as value1

Re: Re: Table: Day - 7 figures issue

Thu, 04 Dec 2014 14:03:01 GMT

Because my fact table is at order line level, not at day level. Peek(value,-7) would not give the amount 7 days ago.

Thanks

Re: Re: Table: Day - 7 figures issue

marcus_sommer — Thu, 04 Dec 2014 15:08:54 GMT

But you could create such consolidated extra table ... you will need make a compromise ...

- Marcus