topic Re: Visual Cues for Annual Subtotal in a Pivot Table in QlikView

Visual Cues for Annual Subtotal in a Pivot Table

Anonymous — Wed, 26 Aug 2015 14:40:21 GMT

I'm trying to identify the ratings that are plus or minus 10% of the corporate average for each year in a pivot table. Both the Major Area and the Year are dimensions in the table.

The expressions I'm using for the visual cue for UPPER is Avg(Rating)*1.1 to highlight in green and for LOWER is Avg(Rating)*.90 to highlight in red.

My problem is that the ratings are highlighted based on the corporate average ratings for ALL the years versus the corporate average rating for EACH year. (For example, 5.64 and 5.67 should also be highlighted in green in year 2012)

What is the proper expression to highlight cells based on the yearly corporate average versus the average of all the years? Thank you!

Re: Visual Cues for Annual Subtotal in a Pivot Table

marcus_sommer — Wed, 26 Aug 2015 14:46:40 GMT

Try: Avg(total <Year> Rating)

- Marcus

Re: Visual Cues for Annual Subtotal in a Pivot Table

ramoncova06 — Wed, 26 Aug 2015 14:56:00 GMT

not sure if it will work but have you tried with total ?

Avg(total<Year>Rating)*1.1

Re: Visual Cues for Annual Subtotal in a Pivot Table

Anonymous — Wed, 26 Aug 2015 15:00:09 GMT

I did try Avg(TOTAL<Year>Rating)*1.1 and it did not work (same result as before)

Re: Visual Cues for Annual Subtotal in a Pivot Table

Anonymous — Wed, 26 Aug 2015 15:00:38 GMT

I did try Avg(TOTAL<Year>Rating)*1.1 and it did not work (same result as before)

Re: Visual Cues for Annual Subtotal in a Pivot Table

ramoncova06 — Wed, 26 Aug 2015 15:03:50 GMT

maybe by using bottom

bottom([ total ] expression [ , offset [,n ]])

Returns the value of expression evaluated with the chart's dimension values as they appear on the last row of the current column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent.

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column.

If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order. The inter field sort order for pivot tables is defined simply by the order of the dimensions from left to right. For other chart types this can be manipulated in the Chart Properties: Sort dialog.

Specifying an offset greater than 1 lets you move the evaluation of expression up to rows above the bottom row. A negative offset number will actually make the bottom function equivalent to a top function with the corresponding positive offset number. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of the last n rows of the current column segment. In this form, the function can be used as an argument to any of the special Chart Range Functions.

Examples:

bottom( sum( Sales ))

bottom( sum( Sales ), 2 )

bottom( total sum( Sales ))

Re: Visual Cues for Annual Subtotal in a Pivot Table

Anonymous — Wed, 26 Aug 2015 15:19:00 GMT

Ramon, unfortunately, the bottom function didn't work either. Thanks for

trying!

Holly

This message is intended for use only by the person(s) addressed above and

may contain privileged and confidential information. Disclosure or use of

this message by any other person is strictly prohibited. If this message

is received in error, please notify the sender immediately and delete this

message.

Re: Visual Cues for Annual Subtotal in a Pivot Table

ramoncova06 — Wed, 26 Aug 2015 15:42:20 GMT

could you share an example ?

Re: Visual Cues for Annual Subtotal in a Pivot Table

petter — Wed, 26 Aug 2015 16:10:43 GMT

The expression you specify in Visual Cue is not aware of the dimensions so Year and Major Area are not considered at all - that is why you get the total corporate for all years.

However you could achieve what you want by abandoning the Visual Cues tab and go to your Expressions tab - select your expression and expand the plus to the left of the expression so you will find "Background Color" - select this and then you will have a corresponding Definition on the right where you could put your expression. This expression will be evaluated in context instead of out of context as with Visual Cues.

So the Definition has to look something like this:

If( Avg( Rating ) > Avg( TOTAL <Year> Rating ) *1.1 , Green(30) , If ( Avg( Rating ) < Avg( TOTAL <Year> Rating ) * 0.9 , Red(30) ) )

Re: Visual Cues for Annual Subtotal in a Pivot Table

Anonymous — Wed, 26 Aug 2015 17:04:00 GMT

Petter, brilliant! That did the trick... thanks for clarifying the issue

and providing the solution!

Holly

This message is intended for use only by the person(s) addressed above and

may contain privileged and confidential information. Disclosure or use of

this message by any other person is strictly prohibited. If this message

is received in error, please notify the sender immediately and delete this

message.

Re: Visual Cues for Annual Subtotal in a Pivot Table

petter — Wed, 26 Aug 2015 17:33:06 GMT

Glad to help. Please close the thread by marking it as answered

Re: Visual Cues for Annual Subtotal in a Pivot Table

petter — Wed, 26 Aug 2015 18:21:37 GMT

I was kind of hoping to score some points for my effort. But since you marked the question as "Assumed Answered" you didn't credit anyone for helping you to solve this...

Re: Visual Cues for Annual Subtotal in a Pivot Table

Anonymous — Wed, 26 Aug 2015 19:54:24 GMT

Petter, sorry, I thought that was the way to close the thread. What

should I do? I didn't see any other way of "closing" this. I certainly

want you to receive credit for your solution... please tell me what I

should do.

Holly

Re: Visual Cues for Annual Subtotal in a Pivot Table

petter — Wed, 26 Aug 2015 20:50:22 GMT

I think you pressed "Mark as Correct" at one of your comments or at your question instead of pressing the "Mark as Correct" on my comment.

There should be an gray button that says "Unmark as Correct" in the place where you marked it initially as correct. If you press that you could reassign it.

Petter S

Re: Visual Cues for Annual Subtotal in a Pivot Table

Anonymous — Wed, 20 Jun 2018 11:04:37 GMT

Thank you very much for such simple solution.