Reduce Pivot columns with a button dynamically to focused values

[petergassert]

Hi,

hopefully this forum is the right address.

My calange is it to implement a button that helps the user to focus and select on the needed data in a Pivot table.

I got two dimensions:
1. Planning Status: in each row
2. Year week forecasting in each column

The meassure calculation is a classical Sum(Fieldname)

Callange is now after selection on the Planning Status to reduce all fields to Max(Planning Status) and (Min. YearWeek) and to select until the Min(Planning Status) and Max(YearWeek) where in all rows information is given.

The actions after activating the button for selection are summarized in the enclosed excel.

Thanks for your help...

[fosuzuki]

Hi,

you have to add set analysis to your measure, to filter the possible YearWeeks. Try something like this:

Sum({<YearWeek={">=$(=Min({<Planning_Status={$(=Max(Planning_Status)}>}YearWeek))<=$(Max({<Planning_Status={$(=Min(Planning_Status)}>}YearWeek))"}>} FieldName)

[petergassert]

Hi Fosuzuki,

thanks for your fast help.
Sadly the formula did not help. May another challange is, that the Planning_Status is a String and only includes Week information. Sorry did not mention this on my side.

A Min and Max Filtering on this string column would be more challanging. 

I tried to adjust your formula with a string formating, but sadly did not work.

Sum({<YearWeek={">=$(=Min({<num#(replace(right(Planning_Status,7),'_',''))={$(=Max(num#(replace(right(Planning_Status,7),'_','')))}>}YearWeek))<=$(Max({<num#(replace(right(Planning_Status,7),'_',''))={$(=Min(num#(replace(right(Planning_Status,7),'_','')))}>}YearWeek))"}>} FieldName)

Thanks and best regards,
Peter

[petergassert · Accepted solution]

Hi Fosuzuki,

thanks again for your hint. 
I found now the solution, as you pointed out how the aim can be reached.

The following formula is now working 

Sum({<YearWeek={">=$(=Min({<Planning_Status={$(=MaxString(Planning_Status))}>} YearWeek))<= $(=Max({<Planning_Status={$(=MinString(Planning_Status))}>} YearWeek))"}>} FieldName)

Thanks again for your help,
Peter