topic Pivot table calculate with hidden values in QlikView

Pivot table calculate with hidden values

Tue, 27 Jan 2015 08:53:35 GMT

Hi,

I have a pivot table with four years.

With expression i calculate sum of "Livraisons" (deliveries).

After, i calculate the difference (delta) with the year before (last column). To do this i use below() function.

It works if i don't use filters years...

Example without filters:

Now i want to filter on 2014 and 2013 :

I use Below function and the result for 2013 is I have an empty value.

How can i use below with hidden lines?

Thanx for your help.

Re: Pivot table calculate with hidden values

Gysbert_Wassenaar — Tue, 27 Jan 2015 10:30:34 GMT

The chart cannot calculate with values that are excluded from the data used in the chart. So you need to include it somehow. You can try creating a new dimension in the script that associates a year with the previous year. See this document for more information: Calculating rolling n-period totals, averages or other aggregations

Re: Pivot table calculate with hidden values

Tue, 27 Jan 2015 12:37:02 GMT

Ok,

I want to use AsOf table.

So i created the talbe and my result is :

I don't know if my result is good...

What do you think?

Re: Pivot table calculate with hidden values

Tue, 27 Jan 2015 16:58:51 GMT

I've worked all my day about this problem.

I've created the AsOf table, but i don't understand how i can use it.

In the example (link), they do a Sum but in my case, i want to substract value so, i don't understand...

Could help me more ?

Re: Pivot table calculate with hidden values

Anonymous — Thu, 29 Jan 2015 13:26:00 GMT

Hello,

you can try with the logic in the qvw attached.

There are 2 variables: it seems to work, when you select couple of consecutive years, but pay attention for couple 2012-2013... I think in this precise case the output is not as you want...

But maybe it can help you.

Let me know!

Elena

Re: Pivot table calculate with hidden values

Gysbert_Wassenaar — Thu, 29 Jan 2015 19:52:03 GMT

Perhaps the attached example helps.