topic Re: SET Analysis Not working within Pick(Match(ValueList())) in App Development

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

mikelutomski — Mon, 04 Feb 2019 16:30:04 GMT

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.

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

sunny_talwar — Mon, 04 Feb 2019 17:20:36 GMT

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

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

mikelutomski — Mon, 04 Feb 2019 17:23:51 GMT

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

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

sunny_talwar — Mon, 04 Feb 2019 17:25:14 GMT

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

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

mikelutomski — Mon, 04 Feb 2019 17:27:00 GMT

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

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

mikelutomski — Mon, 04 Feb 2019 17:41:42 GMT

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.

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

sunny_talwar — Mon, 04 Feb 2019 17:46:14 GMT

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.

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

mikelutomski — Mon, 04 Feb 2019 18:06:41 GMT

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?

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

sunny_talwar — Mon, 04 Feb 2019 18:12:53 GMT

Try this

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

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

mikelutomski — Mon, 04 Feb 2019 18:53:45 GMT

Thanks.

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

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

sunny_talwar — Mon, 04 Feb 2019 19:00:26 GMT

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

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

mikelutomski — Mon, 04 Feb 2019 21:22:18 GMT

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.

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

sunny_talwar — Tue, 05 Feb 2019 12:12:45 GMT

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

What is wrong with this?

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

mikelutomski — Tue, 05 Feb 2019 12:36:29 GMT

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?

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

sunny_talwar — Tue, 05 Feb 2019 14:22:59 GMT

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])
  )    
)

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

mikelutomski — Tue, 05 Feb 2019 17:09:29 GMT

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!

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

THizon1234 — Fri, 22 May 2020 14:15:32 GMT

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