3 Replies Latest reply: Oct 28, 2014 9:21 AM by Jonathan Poole RSS

    Conditionally show expression based on week selection

      Hi All,

       

      Probably a simple answer but currently getting nowhere fast...

       

      I have a pivot table with the following expressions:

       

      wk 38 qty

      wk 38 £

      wk 39 qty

      wk39 £

      wk 40 qty

      wk 40 £

       

      etc etc

       

      I want to only show the week's columns when that week has been selected by the user. Porblem being that the user can select multiple weeks at a time ie weeks 38-41, and i need all of those columns to be shown.

       

      Any help greatly appreciated!

        • Re: Conditionally show expression based on week selection
          Jonathan Poole

          Ideally you have Week as a field in the data model, or at least date and use a pivot table

           

          Then you just need 2 expressions for QTY and £.

           

          Any reason this is not possible ?

            • Re: Conditionally show expression based on week selection

              Originally I did have the chart like this, but there needs to be a column at the end which is only visible for the last week, not for every week. The options are either only show the last column (closing stock) for the last week conditionally, or only show each week when selected conditionally...

                • Re: Conditionally show expression based on week selection
                  Jonathan Poole

                  OK. If i understand correctly , i can say that adding an extra column into the row header of a pivot table is not that straight forward with or without a condition.

                   

                  You can try using the 'conditional' checkbox on each expression and entering a unique condition on each expression.

                   

                  What does the week data look like ? 

                   

                  Lets say your values are:  wk 38, wk 39 etc... and the field is [Week]

                   

                  Then the conditional expression to show wk 38 qty and wk 38 £  expressions would be as follows:

                   

                  substringcount(  concat( Distinct  [Week],'-') , 'wk 38') 

                   

                  ---> The concat funciton will figure out all the possibilities of [Week]  (green and white values) and arrange them in a long list of '-' delimitted values in one string.  The substringcount() will check how many times 'wk 38' appears in that list. If the user selected or indirectly selected (white) it will be > 0 and the condition will be true and the expresison will show

                   

                  You would need to repeat with each expression with a slightly different expression condition .