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]

Would it be possible to share a sample to show the issue?

[mikelutomski]

Let me know if it comes through.

The first sheet is built how I want it to be.

The 2nd sheet is what we've been attempting with some different layouts.

[sunny_talwar]

When you say second sheet, I guess you mean Trend Analysis sheet, right? I am seeing this

What is wrong with this?

[mikelutomski]

Are you seeing two sheets in the application?

The Trend Analysis sheet is what I originally built.  If you select a Date, then it only displays that single Date's results.  It is supposed to show a rolling 12 Months for each Max Date.  If you select 5/1/2018, then it should show results for every date Month between 5/1/2017 thru 5/1/2018.

Do you see the 2nd sheet that is labeled Trend Analysis Testing?

[sunny_talwar]

Okay, I see 3 sheets and I guess I had to use the third sheet. I was not able to do this with just the Island Table, but had to introduce Alternate State? The version of Qlik Sense that you use, does it have Alternate State in it? If you do, then you can try this.... move the chart to the alternate state and change the variables to this

vBegUnitsByDates

num(If(GetSelectedCount([Product Line]) = 0
,
Count(Distinct {<[Site Beginning Site] = {'1'}, [Date]= {'>=$(=AddMonths(Max({$}[Date]),-12))<=$(=Max({$}[Date]))'}>}[Site ID])
,
  if(GetSelectedCount([Natural Class]) = 0
  ,
  Count(Distinct {<[Prod_Beg_Key] = {'Y'}, [Date]= {'>=$(=AddMonths(Max({$}[Date]),-12))<=$(=Max({$}[Date]))'}>}[Site ID])
  ,
  Count(Distinct {<[Beginning Customer] = {'1'}, [Date]= {'>=$(=AddMonths(Max({$}[Date]),-12))<=$(=Max({$}[Date]))'}>}[Site ID])
  )    
),'#,##0')

vBeg$ByDate

If(GetSelectedCount([Product Line]) = 0
,
Sum( {<[Site Beginning Site] = {'1'}, [Date] = {'>=$(=AddMonths(Max({$}[Date]),-12))<=$(=Max({$}[Date]))'}>}[Site Beginning $'s])
,
  if(GetSelectedCount([Natural Class]) = 0
  ,
  Sum( {<[Prod_Beg_Key] = {'Y'}, [Date] = {'>=$(=AddMonths(Max({$}[Date]),-12))<=$(=Max({$}[Date]))'}>}[Beginning $'s])
  ,
  Sum( {<[Beginning Customer] = {'1'}, [Date] = {'>=$(=AddMonths(Max({$}[Date]),-12))<=$(=Max({$}[Date]))'}>}[Beginning $'s])
  )    
)

[mikelutomski]

Unfortunately, we do not have Alternate State added to our version yet.  

If you look at the first sheet 'Summary', you will see a chart on the bottom.  It is using $(vEndingUnitsByDates).  That same variable is in the table on the 2nd sheet.  On the 'Summary' sheet, it is only used with the Date Dimension and NOT being used in a calculated measure.  It's just called by itself as a measure.  

On the 'Trend Analysis' sheet, it's being used in a calculated measure in the Pick(Match(ValueList())).  That is the big difference and the cause of the issue.  I like the use of the Only() function and might replace the ValueList(), but it's not a fix right now.

BTW...Thank you for all of your help so far!  

[THizon1234]

I have the same exact issue. Were you able to resolve this?