Dynamic Table: Straight or Pivot

[#1]

Currently I am having trouble creating a dynamic table, be it straight or pivot. The data within my dimension is Finance Data.Metric. Under this heading lies an array of metrics that we will call Metric 1, Metric 2, .... Each Metric in the table has its own row, and thus own calculation, similar to the following:

Metrics	Deviation
Finance.Metric 1	65.00
Finance.Metric 2	4.00
Finance.Metric 3	55.00
Finance.Metric 4	4.00
Finance.Metric 5	6.00

When nothing is selected I need it to default to the current month, and further, instead of numbers, I want it to read as a symbol arrow facing upwards or downwards depending on its underlying value. So far I have the following:

Within my expression for deviation:

=IF(isnull(GetCurrentSelections([FiscalMonth])),
                             Sum({1<[Finance Data.Metric]={"*"}, [FiscalMonth]={'MAY'}>} [Finance Data.Deviation]),
                             Sum({$<[Finance Data.Metric]={"*"}>} [Finance Data.Deviation]> 0))

Within my the "show value" sub heading I have the following:

=IF(Sum({$<[Finance Data.Metric]={"*"}>} [Finance Data.Deviation])> 0,'▼','▲')

However, when nothing is selected it does indeed default to the values within MAY, but does not display the symbols as specified.

Any help, as always, is appreciated. Thanks in advance.

[swuehl]

Show Value

Click on Show Value in order to enter an attribute expression for calculating whether the data point plot should be complemented with a "value on data point" value, even if Values on Data Points has not been selected for the main expression. If Values on Data Points is selected for the main expression the attribute expression will be disregarded. This type of attribute expression only has effect on bar, line and combo charts. 

And I think it's evaluated to a boolean expression, just telling QV to show or not show the values on data point (i.e. you can't set a symbol here).

If you want to use an up/down array, try a second expression (you can also reference internal images and then set representation to image instead of text in Display options).

[#3]

Thanks for the fast reply, I ended up figuring out the problem. I had an error within my expression. Everything is up and running now.

=IF(isnull(GetCurrentSelections([FiscalMonth])),
                   Sum({1<[Finance Data.Metric]=, [FiscalMonth]={'MAY'}>} [Finance Data.Deviation]),
                  Sum({$<[Finance Data.Metric]=>} [Finance Data.Deviation]))

And then added another expression as follows:

IF(Sum({$<[Finance Data.Metric]=>} [Finance Data.Deviation]) < 0,'▼','▲')

The result can be seen below:

Metric	Deviation
Finance.Metric 1	0.000	▼
Finance.Metric 2	0.096	▼
Finance.Metric 3	0.013	▲
Finance.Metric 4	0.009	▲
Finance.Metric 5	4.756	▲
-	0.000	▲

Now I have a new problem, how do I remove that last row?

[swuehl]

Have you tried 'Supress when value is NULL' on dimension tab?

[#5]

yes, it does not work. However, if I hide my second expression

IF(Sum({$<[Finance Data.Metric]=>} [Finance Data.Deviation]) < 0,'▼','▲')

The bottom row disappears.

[sunny_talwar]

Replace your second expression to be conditional on first:

If(Expression1 <> 0, IF(Sum({$<[Finance Data.Metric]=>} [Finance Data.Deviation]) < 0,'▼','▲'))

[sunny_talwar · Accepted solution]

If expression 1's label is Deviation, you can try this:

If([Deviation] <> 0, IF(Sum({$<[Finance Data.Metric]=>} [Finance Data.Deviation]) < 0,'▼','▲'))

Note: Deviation's color should change to blue if it is truely your label for expression 1

HTH

Best,

Sunny

[#8]

Bingo. Didn't know I could reference expression labels. Thanks!

[sunny_talwar]

Now you do

There are couple of ways to do it. One is to use the name or the other is to use Column() function. So for example if you want to refer your first expression, you can use Column(1) = blah blah blah....

Best,

Sunny

[#10]

Excellent, for this instance I wanted my heading to remain hidden, thus I named it " ", that being just a space. Using,

If(column(1) <> 0, IF(Sum({$<[Finance Data.Metric]=>} [Finance Data.Deviation]) < 0,'▼','▲')) 

Makes it far more legible than compared to:

If(" " <> 0, IF(Sum({$<[Finance Data.Metric]=>} [Finance Data.Deviation]) < 0,'▼','▲'))