SET Analysis Not working within Pick(Match(ValueList()))

[mikelutomski]

I'm using Sense September 2018 Patch 2.

I'm having an issue with SET analysis formulas that are supposed to show a rolling 12 months, regardless if a Date is selected or not.

The below formulas work on their own with a Date dimension in a straight table, pivot table or chart.  

Once it's placed into a Pick(Match(ValueList()))), it only displays the selected date and not the rolling 12 months.

vBegUnitsByDates = Count(Distinct {$<[Beginning Site] = {'1'}, [Date]= {'>=$(=AddMonths(Max([Date]),-12))<=$(=Max([Date]))'}>}[Site ID])

vBeg$ByDate = Sum( {<[Beginning Site] = {'1'}, [Date] = {'>=$(=AddMonths(Max([Date]),-12))<=$(=Max([Date]))'}>}[Site Beginning $'s])

=Pick(Match(ValueList('Customers',
'Dollars',
'Avg $/Customers'),
'Customers',
'Dollars',
'Avg $/Customers'),
num($(vBegUnitsByDates),'#,##0'),
num($(vBeg$ByDate),'$#,##0'),
num(($(vBeg$ByDate))/($(vBegUnitsByDates)),'$#,##0')
)

I've tried to use the formula in the Pick(Match(ValueList())) instead of the variable, but get the same results.  

Please let me know if anybody needs more information that could help clarify my problem.

[sunny_talwar]

What all dimensions do you have in the chart except for the ValueList() dimension?

[mikelutomski]

Other than the ValueList() I am only including the Date dimension as I want to see the Rolling 12 Months.

[sunny_talwar]

okay and do you see any value getting populated in the chart or is it completely null?

[mikelutomski]

I am getting results, but ONLY for the month selected.  

If I select Jan-2018, I want to see Jan-2017 thru Jan-2018.  However, I am only seeing Jan-2018

[mikelutomski]

I guess I should also mention that if NO Date is selected, I get the Rolling 12 Months based on the Max Date.

It's only when I select a Date that the SET Analysis doesn't work as it should.

[sunny_talwar]

I suggest you to create an Island table in the script to do this instead of using ValueList(). The benefit is that you cannot ignore selections in a valulist() dimension, where as an island table dimension can be specified like this

Only({1} IslandTableDim)

Allowing you to ignore selections.

[mikelutomski]

I created an Inline table that consists of the ValueList() values, but I'm not sure how to use it with the variable considering it is not associated with the source table that the variable is calculating.

LOAD * INLINE [
"Trend Analysis"
"Customers",
"Dollars",
"Avg $/Customers"
] ;

I tried using it with an IF(), but got the same results as the ValueList().

if([Trend Analysis] = 'Customers', $(vBegUnitsByDates),
  if([Trend Analysis] = 'Dollars', $(vBeg$ByDate),
    if([Trend Analysis] = 'Avg $/Customers', $(vBeg$ByDate) / $(vBegUnitsByDates))))

How can I get this to work with the ONLY() function?

[sunny_talwar]

Try this

Pick(Match(Only({1} [Trend Analysis]),
  'Customers',
  'Dollars',
  'Avg $/Customers'),
 
 $(vBegUnitsByDates),
  $(vBeg$ByDate),
  $(vBeg$ByDate) / $(vBegUnitsByDates)
)

[mikelutomski]

Thanks.

I dropped that into the table and I still got the same results for the single month when a Date was selected.