5 Replies Latest reply: Sep 13, 2016 7:44 AM by Oliver Annells RSS

    straight table dynamic formatting issue

    Oliver Annells

      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

       

        • Re: straight table dynamic formatting issue
          Andrew Walker

          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

          • Re: straight table dynamic formatting issue
            Toni Kautto

            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?

              • Re: straight table dynamic formatting issue
                Oliver Annells

                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):

                Table after selection.png

                Before selection (value formatting working dynamically)

                Table before selection.png

                Before selection (qty formatting working dynamically)

                Table before selection2.png

                Expression

                Expression.png