Hi,
I need help in building the formula for the below business case :
Regards,
Adil
Question: why 50, not 60 - what is the business rule behind that?
the requirement is to get the first opening sale of the day , the first shift of the salesman
Thanks. First step, create a Measure Variable, call it RankTourID and the formula is simply:
=[Tour ID]
This is because you can only rank by measures and Tour ID is a dimension variable.
Next, create a variable called Day to strip the time from your Start Date\Time object as follows:
=ToDate(FormatDate([Start Date\Time];"yyyyMMdd");"yyyyMMdd")
Swap this in to your block instead of your Start Date\Time object.
Now, you can use the ranking workflow (the podium icon) to build your ranking. In the ranking box, set the following options:
Top - make sure this is unchecked
Bottom - make sure this is checked and choose 1
Based on - choose Rank Tour ID
Ranked by - choose Day
Calculation Mode - choose Count
Now delete Tour ID from your table and change the Sales label.
You can also right click on your Day data to format the date to display as you want it to - do not make the common mistake of using a formatting variable because the sort order will then be incorrect.
1 Like