topic Re: Set Analysis with Value List with Restricting filters in App Development

Set Analysis with Value List with Restricting filters

SarahD — Wed, 04 May 2022 22:57:03 GMT

I am trying to create a line graph with 4 trend lines that show the last 13 months and only filter on DEPT_GROUP_DESCRIPTION.
The value list makes it so the set analysis part doesn't work (aka, the timeframe filters apply and causes the line not to show the 13 months).

The value list comes from 4 different columns that contain their own list of names.

IF( ValueList(vInternal,vSPC,vPartner,vCenter)=vInternal,
(sum({1<[CENTER_TYPE]={'Inhouse'},[METRIC_ID]={282},DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION)>}RESULT_NUM)
/
sum({1<[CENTER_TYPE]={'Inhouse'},[METRIC_ID]={282},DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION)>}RESULT_DEN))
,
IF( ValueList(vInternal,vSPC,vPartner,vCenter)=vSPC,
(sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282},DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION), TIMEFRAME=>}RESULT_NUM)
/
sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282},DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION), TIMEFRAME=>}RESULT_DEN)),
IF( ValueList(vInternal,vSPC,vPartner,vCenter)=vPartner,
(sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282},VIRTUAL_LOCATION_COMPANY_ID=P(VIRTUAL_LOCATION_COMPANY_ID),DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION), TIMEFRAME=>}RESULT_NUM)
/
sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282},VIRTUAL_LOCATION_COMPANY_ID=P(VIRTUAL_LOCATION_COMPANY_ID), DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION), TIMEFRAME=>}RESULT_DEN)),
IF( ValueList(vInternal,vSPC,vPartner,vCenter)=vCenter,
(sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282},VIRTUAL_LOCATION_COMPANY_ID=P(VIRTUAL_LOCATION_COMPANY_ID),GEOGRAPHIC_LOCATION_DESCRIPTION=P(GEOGRAPHIC_LOCATION_DESCRIPTION), DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION), TIMEFRAME=>}RESULT_NUM)
/
sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282},VIRTUAL_LOCATION_COMPANY_ID=P(VIRTUAL_LOCATION_COMPANY_ID),GEOGRAPHIC_LOCATION_DESCRIPTION=P(GEOGRAPHIC_LOCATION_DESCRIPTION), DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION), TIMEFRAME=>}RESULT_DEN)),

)))
)

I've also tried this and it works only for the first line, I can't figure out how to get it to work for the rest of the lines so its all in one chart:

IF( ValueList(vInternal,vSPC,vPartner,vCenter,vMinTimeframe)=vInternal, vMinTimeframe,
(sum({1<[CENTER_TYPE]={'Inhouse'},[METRIC_ID]={282},DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION)>}RESULT_NUM)
/
sum({1<[CENTER_TYPE]={'Inhouse'},[METRIC_ID]={282},DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION)>}RESULT_DEN)))

Re: Set Analysis with Value List with Restricting filters

vinieme12 — Thu, 05 May 2022 04:36:16 GMT

The Timeframe filters are getting applied due to below

DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION)

This will only return DEPT_GROUP_DESCRIPTION that exists in the selected TIMEFRAME which is correct

It is not one filters the other, but everything filters everything

if you filter on TIMEFRAME you get all possible DEPT_GROUP_DESCRIPTION for selected TIMEFRAME

if you filter DEPT_GROUP_DESCRIPTION , you get all possible TIMEFRAME for selected DEPT_GROUP_DESCRIPTION

As you only want DEPT_GROUP_DESCRIPTION to be filtered that would also mean data is only shown for the DEPT_GROUP_DESCRIPTION for the available dates those DEPT_GROUP_DESCRIPTION exist

also adding this is redundant as responding to selections in default behavior

DEPT_GROUP_DESCRIPTION=P(DEPT_GROUP_DESCRIPTION)

part 1/2

Re: Set Analysis with Value List with Restricting filters

vinieme12 — Thu, 05 May 2022 04:35:14 GMT

part 2/2

the below will work the same way

Also how exactly are you filtering last12 months??

IF( ValueList(vInternal,vSPC,vPartner,vCenter)=vInternal,
(sum({1<[CENTER_TYPE]={'Inhouse'},[METRIC_ID]={282}>}RESULT_NUM)
/
sum({1<[CENTER_TYPE]={'Inhouse'},[METRIC_ID]={282}>}RESULT_DEN))
,
IF( ValueList(vInternal,vSPC,vPartner,vCenter)=vSPC,
(sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282},TIMEFRAME=>}RESULT_NUM)
/
sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282},, TIMEFRAME=>}RESULT_DEN)),
IF( ValueList(vInternal,vSPC,vPartner,vCenter)=vPartner,
(sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282}, TIMEFRAME=>}RESULT_NUM)
/
sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282}, TIMEFRAME=>}RESULT_DEN)),
IF( ValueList(vInternal,vSPC,vPartner,vCenter)=vCenter,
(sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282}, TIMEFRAME=>}RESULT_NUM)
/
sum({1<[CENTER_TYPE]={'SPC'},[METRIC_ID]={282}, TIMEFRAME=>}RESULT_DEN)),

)))
)

Re: Set Analysis with Value List with Restricting filters

SarahD — Thu, 05 May 2022 18:36:38 GMT

Thanks for the response...
Timeframe is one of the filters. I need the timeframe to filter some of the numbers (the ones on the bottom), but I need the Trending lines to NOT filter on the timeframe. I created the set analysis and it works beautifully until I add the value list, then the set analysis part stop functioning correctly.

Re: Set Analysis with Value List with Restricting filters

vinieme12 — Fri, 06 May 2022 05:25:08 GMT

the use of valuelist() does not change how set analysis works, as I confirmed above, your selections in other fields are affecting the set scope

Can you confirm how exactly are you restricting the last 13 months period in your chart?

Please post all details regarding dimensions /measures used in the chart

Re: Set Analysis with Value List with Restricting filters

SarahD — Mon, 09 May 2022 20:53:40 GMT

Q: Can you confirm how exactly are you restricting the last 13 months period in your chart?
I'm not restricting it, nor trying to. The data has 13 months and I want to include all of those in the line graph. I am trying to make it so TIMEFRAME does NOT filter this expression. Any ideas?