Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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).
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?
Have you tried 'Supress when value is NULL' on dimension tab?
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.
Replace your second expression to be conditional on first:
If(Expression1 <> 0, IF(Sum({$<[Finance Data.Metric]=>} [Finance Data.Deviation]) < 0,'▼','▲'))
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
Bingo. Didn't know I could reference expression labels. Thanks!
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
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,'▼','▲'))