Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
Currently, I have a drill-down chart with Region->City and I have 5 expressions say A,B,C,D & E. I see 5 bars(A,B,C,D,E) when on "Region" or when I drill down to "City". But B,C,D & E are subset of A. So, I want to see only 1 bar (A) when in "Region" or "City" and I would like to see the subsets (B,C,D,E) only when I make a selection in City.
So, my idea is that I have to create a new level "Values" on the script side and define a hierarchy (Region-> City-> Values). And this "Values" contains B,C,D & E.
Could someone help me in creating this, the script side of it?
The expression that I use now for A,B,C,D,E is something like this:
=COUNT({<Date=, Year=, Month=, Day= >} DISTINCT IF((EVT_RSTATUS = 'R' or EVT_RSTATUS = 'Q') and EVT_SCHEDEND < today() and EVT_STATUS <> 'S'
and EVT_JOBTYPE='PM'
,EVT_CODE))
EVT_JOBTYPE is different for A,B,C,D & E
Thank you very much.
What is the reason for creating a 5 expressions. Are these different fields within a single table? May be you want to transpose your data into rows instead of having them as separate columns using The Crosstable Load
Hi Sunny,
The reason why there are 5 expressions is that they have different values for EVT_JOBTYPE in the expression I've provided. eg: A: EVT_JOBYTYPE='PM'
B: EVT_JOBYTYPE='OT'
& so on.
I don't think changing the rows into columns is a good idea, because it is a huge database and if I make that change then it would impact the many expressions I've used on the Layout.
Hi Divya,
You dont need to create 5 expressions.
Create one variable with this:
YourVar content:
=COUNT({<Date=, Year=, Month=, Day= >} DISTINCT IF((EVT_RSTATUS = 'R' or EVT_RSTATUS = 'Q') and EVT_SCHEDEND < today() and EVT_STATUS <> 'S' and EVT_JOBTYPE=$1 ,EVT_CODE))
When you call your var:
$(YourVar('PM'))
Then, you can simplify your expression:
=COUNT({<Date=, Year=, Month=, Day=,EVT_RSTATUS = {'R','Q'},EVT_STATUS=-{'S'},EVT_JOBTYPE={$1}>} IF(EVT_SCHEDEND < today(),EVT_CODE))
Then, you can add more than one EVT_JOBTYPE, like this: $(YourVar('PM,OP1,OP2,OP3,...'))
Regards!!!
Hi Manuel,
So you mean, I must create a variable
vVar=COUNT({<Date=, Year=, Month=, Day=,EVT_RSTATUS = {'R','Q'},EVT_STATUS=-{'S'},EVT_JOBTYPE={$1}>} IF(EVT_SCHEDEND < today(),EVT_CODE))
and have 1 expression for B,C,D & E as say
B: $(YourVar('PM'))
C: $(YourVar('OT'))
& so on.
right?
Hi Divya,
Yes, you can use variables like a function using parameters. $1 is your parameter, and you pass the values like this $(YourVar(ParameterValue)). If your parameter is a string or a formated date, use single quotes.
Regards!!!