Help - Rolling Average Issue With User Selection

[#1]

Hello Everyone,

This is an edit of my previous question.

I have a rolling average calculation that is correct. However, when the user selects a time period less than the 13 Months required for my rolling average, my pivot table only uses the months that are selected and does not "look back" further to produce the correct data.

The calculation I am using is as follows:

(after(sum([Inventory Value])/sum([AvgCOSG]),0,13))

Attached is an excel sheet with the data used for the calculations, the answers as they appear in Qlikview and the correct answers.

Any help is greatly appreciated! To anyone who has helped earlier your help is greatly appreciated and I thank you for your time

As an update, the answer marked correct helped to fix an issue with graying/omitting data unneeded on the table which was a larger scope of the issue.

[MK_QSL]

Can you provide little sample data and example ?

[#3]

"What would be the best way to go about restricting selections in expressions but using them in the table as a filter?"

If you are restricting the selection by a set analysis (for example {<USER_ID=>}) ignoring the USER_ID, then you are telling to not filter by this.

So they will be shown in the table, and if you select any USER it will be ignored.

[#4]

Would you be able to provide some insight on how to ignore selections in an expression, but have them used when displaying data in the table?

I apologize on my end, I'm having a bit of trouble trying to put words to my problem.

[#5]

Hi Christian,

it would be good if you write an example of what is happening.

I suppose that you have a pivot table using an expression with a set expression restricting the filtering for user. And then when you select one user, you only want to show in the table the data of that user.

Am I correct?

[#6]

Hey Christian,

I will take shoot.

Lets say you have the table below:

Dim1	Dim2	Dim3	Expression1
A	a	X	25
A	a	X	35
A	a	Y	52
A	b	Z	40
B	c	Z	125
B	c	Z	172
B	c	X	183
B	d	Y	159
C	e	X	325
C	f	Y	248

Now you want to sum Expression1 in a pivot table, but only if Dim3 is equal to either 'X' or 'Y'.

Your set analysis would be:

=sum({1<Dim3={'X','Y'}>}Expression1).

If you want to restrict data based on Dim1 or Dim2, your set analysis could be:

=sum({1<Dim3={'X','Y'},Dim1=$::Dim1,Dim2=$::Dim2>}Expression1).

Your pivot table would then look like this:

Dim1	Dim2	=sum({1<Dim3={'X','Y'},Dim1=$::Dim1,Dim2=$::Dim2>}Expression1)
A	a	112
B	c	183
	d	159
C	e	325
	f	248

There is probably a better way, but hope it can help you.

Best Regards

Andy

[#7]

Hi Juan,

So the expression calculates a rolling 13-Month average. A-D are products, and time is on the top (from Jan, 2010 to May 2014). We found that without using a set analysis, if the user selected a specific month, product or quarter, the calculation would be wrong as it would only use one month.

So, we decided to use set analysis and ignore month, year, quarter and product. When the user makes a selection, the calculation is correct and displays. However, the table then displays the rest of the data and ignores the user selection.

Our real problem is trying to allow the calculation to ignore a user selection (as if a month/quarter is selected, it would make the calculations based on the selections and not the rolling 13M average required) and then restrict the output to the table based on the user selection.

I hope that clarified my problem a little. I apologize again for a bit of confusion on my end. Thank you

[#8 · Accepted solution]

It is not possible to do it, but you can change the background color depending on the selections you make, like  QV style.

Creating the variables you need: vYear = Year, vQuarter = Quarter, vMonth = Month ...

Then in the Pivot Table -> Properties -> Dimension tab (for each) -> Background Color:

     if(Quarter=vQuarter,White(),lightGray())

     ...

When you select a Quarter, the background color will turn white, and the others gray. But it also will paint in gray the inner cells of the pivot table referred to it.

Also do it for the others dimensions.

It's not exactly what you are looking for, but it could be a good solution for your needs.

[#9]

Thank you for your reply and apologies for getting back to you late.

I was able to fix the table with much of your suggestion. An issue I was facing was the year/month column being out of order which was impacting the calculation.