Conditionally show expression based on week selection

[#1]

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!

[JonnyPoole]

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 ?

[#3]

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...

[JonnyPoole]

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 .