Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
I have a line chart and I would like to expand the expression so that selecting options from two field lists do not impact the line chart. The expression is:
If(Aggr (NODISTINCT rank(
count({<Phase={5}, Q11_Selection={"Selected"}>} RESPID)/
count({<Phase={5}, Q11_Destination=>} distinct RESPID)), Q11_Destination)
< 11 or Q11_Destination='Australia',
rank(
if(Phase=1,
Count({<Phase= {1}, Q11_Selection={"Selected"}>}RESPID)/
Count({<Phase= {1}, Q11_Destination=>} distinct RESPID),
if(Phase=2,
Count({<Phase= {2}, Q11_Selection={"Selected"}>}RESPID)/
Count({<Phase= {2}, Q11_Destination=>} distinct RESPID),
if(Phase=3,
Count({<Phase= {3}, Q11_Selection={"Selected"}>}RESPID)/
Count({<Phase= {3}, Q11_Destination=>} distinct RESPID),
if(Phase=4,
Count({<Phase= {4}, Q11_Selection={"Selected"}>}RESPID)/
Count({<Phase= {4}, Q11_Destination=>} distinct RESPID),
if(Phase=5,
Count({<Phase= {5}, Q11_Selection={"Selected"}>}RESPID)/
Count({<Phase= {5}, Q11_Destination=>} distinct RESPID),
)))))
)*-1
)
I have filters with Phase and Q11_Destination dimensions that I would like to be ignored by the line chart when selections are made. Is there a way I can work this into the expression? Adding Q11_Destination= as I have done above doesn't seem to make any difference. The qvf file is attached.
Thanks in advance
Emma
May be try this
If(Only({<Phase, Q11_Region>} Aggr (NODISTINCT rank(
count({<Phase={5}, Q11_Selection={"Selected"}, Q11_Region>} RESPID)/
count({<Phase={5}, Q11_Destination=, Q11_Region>} distinct RESPID)), Q11_Destination))
< 11 or Only({<Phase, Q11_Region>}Q11_Destination) = 'Australia',
rank(
if(Only({<Phase, Q11_Region>}Phase)=1,
Count({<Phase= {1}, Q11_Selection={"Selected"}, Q11_Region>}RESPID)/
Count({<Phase= {1}, Q11_Destination=, Q11_Region>} distinct RESPID),
if(Only({<Phase, Q11_Region>}Phase)=2,
Count({<Phase= {2}, Q11_Selection={"Selected"}, Q11_Region>}RESPID)/
Count({<Phase= {2}, Q11_Destination=, Q11_Region>} distinct RESPID),
if(Only({<Phase, Q11_Region>}Phase)=3,
Count({<Phase= {3}, Q11_Selection={"Selected"}, Q11_Region>}RESPID)/
Count({<Phase= {3}, Q11_Destination=, Q11_Region>} distinct RESPID),
if(Only({<Phase, Q11_Region>}Phase)=4,
Count({<Phase= {4}, Q11_Selection={"Selected"}, Q11_Region>}RESPID)/
Count({<Phase= {4}, Q11_Destination=, Q11_Region>} distinct RESPID),
if(Only({<Phase, Q11_Region>}Phase)=5,
Count({<Phase= {5}, Q11_Selection={"Selected"}, Q11_Region>}RESPID)/
Count({<Phase= {5}, Q11_Destination=, Q11_Region>} distinct RESPID),
)))))
)*-1
)
Hi Sunny
That method works really well for the Phase variable. The other fixed variable I would like is Q11_Destination so I have changed this in the 'only' statements to: Only({<Phase, Q11_Destination>}...
However, if I select a single destination on the bar chart, the line chart will update that destination's rank to be 1-1-1-1-1 across the five phases. e.g. if I select Japan on the bar chart, the line chart changes to 1-1-1-1-1 where it should be 6-6-3-2-2. The rank still needs to be calculated out of all 50-or so destinations.
Any help would be much appreciated!
Thanks
Emma
Try this
If(Only({<Phase, Q11_Destination>} Aggr (NODISTINCT rank(
count({<Phase={5}, Q11_Selection={"Selected"}, Q11_Destination>} RESPID)/
count({<Phase={5}, Q11_Destination=>} distinct RESPID)), Q11_Destination))
< 11 or Only({<Phase, Q11_Destination>}Q11_Destination) = 'Australia',
rank(
if(Only({<Phase, Q11_Destination>}Phase)=1,
Count({<Phase= {1}, Q11_Selection={"Selected"}, Q11_Destination>}RESPID)/
Count({<Phase= {1}, Q11_Destination=>} distinct RESPID),
if(Only({<Phase, Q11_Destination>}Phase)=2,
Count({<Phase= {2}, Q11_Selection={"Selected"}, Q11_Destination>}RESPID)/
Count({<Phase= {2}, Q11_Destination=>} distinct RESPID),
if(Only({<Phase, Q11_Destination>}Phase)=3,
Count({<Phase= {3}, Q11_Selection={"Selected"}, Q11_Destination>}RESPID)/
Count({<Phase= {3}, Q11_Destination=>} distinct RESPID),
if(Only({<Phase, Q11_Destination>}Phase)=4,
Count({<Phase= {4}, Q11_Selection={"Selected"}, Q11_Destination>}RESPID)/
Count({<Phase= {4}, Q11_Destination=>} distinct RESPID),
if(Only({<Phase, Q11_Destination>}Phase)=5,
Count({<Phase= {5}, Q11_Selection={"Selected"}, Q11_Destination>}RESPID)/
Count({<Phase= {5}, Q11_Destination=>} distinct RESPID),
)))))
)*-1
)
Hi Sunny
Yes, that's it. Thanks very much!
Emma