Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)))
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
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)),
)))
)
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.
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
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?