Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Have a look at the attached file for better understanding of the scenarios.
There is a table box and a pivot table (format is there). My desired layout will be on the pivot table where it hasa simple Year, Week and number of bus with special condition, this can be done by using Count(Bus)
as you can see from the table box, these are the buses that are being deploy on 2012 Week 2. Actual time of boarding is the time they left the paritcular bus_terminal. For first row, HAN LINN departed bus terminl call PPI1 at jan 10 2012.
In cases where bus have to move to another terminal due to shortage of buses over the other terminal, it will be call berthing sequence 2. Example will be bus TATA MACHAN, it first berth was at PPI1 and move to KI interchange which shows 2.
Therefore, I would want to count the number of buses that has move from terminal to another terminal.
The next condition is, if the bus move from within terminal saying SAN CALYPS where its berthing shift 1 and 2 is the same TPI, it should exclude from the counting of buses. This goes to TATA HADIAH where it shifted from PPI1 to PPI2 as PPI represent the same whole bus interchange.
I would want to count bus such as JINYUNHE where it move from KI to PPI1, these are count as one bus that really deploy to other terminal.
Is there anyway I can do in a single expression?
Correct result will be 3 which is JINYUNHE, TATA LAMBANG and TATA MACHAN
Hi Benn,
Some calculation in script will make this expression easier. please refer to attached application which i modified from your original application. hope it helps
possible to do these in expression as I am unable to edit anything from the script
Here is the expression you want
Count
(DISTINCT Aggr(If(Left(MaxString(Bus_Terminal_ID),2)<>(Left(MinString(Bus_Terminal_ID),2)),Bus_Name),Bus_Name))
Thanks, is getting there. But i need to check in berthing sequence as if this identical bus will to have many rows with one berthing sequence without changing any terminal, it will still take in as one count as per ur code
This will work
=
Sum(Aggr(Count(DISTINCT Left(Bus_Terminal_ID,2))-1,Bus_Name))
This code works!
Count
(DISTINCT Aggr(If((Left(MaxString(Bus_Terminal_Id),2) <>(Left(MinString(Bus_Terminal_Id),2)))
and Left(MaxString(Berthing_Seqence),1) <> Left(MinString(Berthing_Seqence),1)
,Bus_Name),Bus_Name,Week,Year))
My last issue is, hope you can help
If bus terminal will to move between KI and TI , it will not counted as one. This is like PPI1 and PPI2. This is tricky
Is there additional code that I can add in the the code above saying that if bus terminal move between KI and TI, do not count as one
Hi benn,
Your expression is getting too complex, it is advisable if you get your script modified and handle few scenarios in your script.
But my expression is nearly there, just need to add in one more condition to
(DISTINCT Aggr(If((Left(MaxString(Bus_Terminal_Id),2) <>(Left(MinString(Bus_Terminal_Id),2)))
and Left(MaxString(Berthing_Seqence),1) <> Left(MinString(Berthing_Seqence),1)
,Bus_Name),Bus_Name,Week,Year))
My last issue is, hope you can help