Returning a text field in a straigh table (expression)

[#1]

Hi all,

Am trying to return the currency name within the attached document, within the body of my straight table. As this field is not a number, I shouldn't use sum, or avg etc...

How do I return a text value?

Please advise,

Kind regards,

Rich

[matt_crowther · Accepted solution]

Rich,

Solution attached.

In instances like that where you need to 'aggregate' (ie Sum / Avg / Max etc) but it's a value that can't be aggregated use the only() function. As a Set has to be Analysed you need to perform some kind of aggregation and fortunately only() is classed as an aggregation.

See the Help file for a more useful explanation!

All the best,

Matt - Visual Analytics Ltd

[#3]

You can use Only to get the only possible value (see attached file), or Concat to get all possible values joined in one text string.

[#4]

if there are mulitple currencies being used for say Material 1 (eg Euros, GBP and USD), for the same period, what is the best way of splitting this out so that I am always comparing one currency against another?

Should I move the FXCurrency (for Period 1) back to the dimension (which would then report 3 lines for material 1:

Material 1 Euros

Material 1 GBP

Material 1 USD

This would then ensure that I have a single currency being displayed for P1. How can I then compare this against P2 which may also have multiple currencies.

ie for Material 1, Euros, Period 1 the associated P2 currencies still could be a mixture of GBP, Euros, and USD?

[#5]

have added a revised file, to take into account multiple currencies appearing in the periods...

[matt_crowther]

Rich,

I'd suggest putting 'FXCurrency' as your second dimension thus giving you multiple lines for a single material where there are multiple currencies.

This may not exactly be what you require but otherwise it will be difficult to achieve what you need on a single line.

You could of course switch to a piviot table to get the best of both worlds - see attached.

Hope that helps,

Matt - Visaul Analytics Ltd

[#7]

Once again thanks Matt!

Once I have been able to formulate what i want to achieve in a straight table, typically, I convert the straight table back into a waterfall chart.

By using a pivot table, I am assuming it will be hard to replicate this type of analysis back in a graph?

ie I just use straight tables / pivot tables to ensure calculations are correct, and then ultimately only display a graph once happy that the calcs work.

Do you agree?

[matt_crowther]

Rich,

I'd agree with that - I generally only use Pivot Tables when their visual type is needed not their functionality - ie; I want to show sub-totals or have months running across the top without having to add an expression for each. The majority of things that can be created in a Pivot can be reproduced in a Straight Table (and therefore Chart as all charts have a Stright Table in the background) only they look slightly different - I find Straight Tables far more intuative and easier to develop with.

All the best,

Matt - Visual Analytics Ltd

[matt_crowther]

Rich,

Not understanding entirely what your users need you to convey makes it a little difficult to advise on how best to present the Pivot Table graphically however as a general rule trying to cram loads of information into a chart just leads to the key elements getting lost in the fuzz.

I find it better to use either a Pivot Table in conjunction with a chart perhaps containing the 2 key measures to point you in the right direction or even just a Pivot / Striaght with visual cues to highlight areas of interest or alternatively a simpler chart where the user can use selections to cycle through the data.

I've attached an example of how far I feel you can push the visualisation of the pivot data without it getting overly cluttered (others would of course disagree) and thus loosing the message. Putting lesser values as either 'Text as Pop-up' of 'Text on Axis work well in my experience. Essentially in the attached we're showing the same info as the full pivot but potentially in a way that suits teh user experience much better.

It always has to be remembered that information that can sit quite happily together in a Striaght Table or Pivot chart doesn't always work in a visual chart.

All the best,

Matt - Visual Analytics Ltd