straight table dynamic formatting issue

[#1]

Hi

I have a built a straight table with a single dimension and expression.  The table shows the sales by manager for a selected date period.  To try and maximise space and efficiency I am trying to make the table dynamic so the user can choose to show the value in qty sold or value of goods sold.

I created an orphan table with two fields (_metric and _metric#) with the following values:

Value | 1

Volume | 2

I have wrapped the expression (which is working as desired) in the num function and am using the pick function to select an appropriate number format.

The problem I am encountering is when a selection is made on a sales person (under a manager) the values remain correctly unchanged but the number formatting is lost from all but the managers row

This is an example (value) before selection is made

manager1     |     £123,456

manager2     |     £234,567

manager3     |     £121,212 

This is an example (value) after a salesperson for manager 3 is selected

manager1     |        123456

manager2     |        234567

manager3     |     £121,212

This is the expression:

=num(sum({1<_metric#=p(_metric#),_metric=p(_metric),_Type={'Standard'},Tier0={'Regional'}

        ,Date={">=$(=num(min({1<Date=p(Date),_metric#=p(_metric#),_metric=p(_metric)>} Date)))<=$(=num(max({1<Date=p(Date),_metric#=p(_metric#),_metric=p(_metric)>} Date)))"}

    >}pick(_metric#,[#Line Value],[#Qty Shipped])),pick(_metric#,'#,##0.00','#,##0'))

As the niether the num or pick function allow set analysis (set identifiers) I have not idea why this is happening.

Any ideas?

Thanks

Oli

[effinty2112 · Accepted solution]

Hi Oli,

          Wow - your expression has my head in knots! Can I offer an alternative solution? Why not have two expressions in your table, Value and Volume and make them both conditional on the value of _metric#?

I used to make a lot of charts with complicated expression whose definitions changed with selections but that can get really hard to maintain. I still have a stack of qvw with charts with expressions like that and some of them are murder to change then get working again.

Now, if I can, I always add multiple expressions and make their appearance condition rather than have one expression whose definition is conditional. The problem of formatting depending on the value of selections or variables disappears.

Hope this helps.

Cheers

Andrew

[#3]

Hi Andrew

An alternative solution is as good as a fix and I will always listen to good advice - K.I.S.S

Thanks

Oli

[effinty2112]

Hi Oli,

          The quickness with which you marked my response suggests that you may have been thinking along these lines anyway. It's hard sometimes when you write something that is very complicated and just about works and you like the logic behind it but you get the feeling that you're painting yourself into a corner but you're reluctant to give it up.

I don't know if that's the case for you but I've been there many times and had to make a tactical retreat.

Glad to have helped.

Cheers

Andrew

[ToniKautto]

I am having a hard time understanding your expression. Would it be possible for you to attach a limited sample QVW?

For the description I think you might be over using the num() formatting. For example if you have a date field, the expectation is that this field looks like dates and has a integer underlying number representation. If you aggregate the smallest value int he field, it will return a integer. To format the integer to a number should not add any value.

=num(min(Date))

For the format pattern, wouldn't it be easier to have a variable that represents the Pick() part of your expression? Then you could just expand the variable into the format parameter in any function where you need it?

[#6]

Hi

I can't easily attached any sample data but maybe some photos will clarify what I am experiencing.

I understand what you regarding the date/num format.  All my dates for set analysis etc are serial numbers for ease and I was formating them after the min/max just to ensure they remained so.  I see after removing them that this was unnecessary; thank you.

After selection (formatting mostly lost):

Before selection (value formatting working dynamically)

Before selection (qty formatting working dynamically)

Expression