Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts...
Need your help!
We have list of Branches with their Opening/Start Date.
Also, have prepared a table showing Trading Day... i.e. 1 for Full Day, 0.5 for Saturday and 0 for Holiday
I have created two variables
vFromDate and vToDate
Now Question is :
Need a Final Straight or Pivot Table showing Total Trading Days based on Branch Level.
If vFromDate > BranchStartDate,
SUM of Trading Day between vFromDate to vToDate
else
SUM of Trading Day between BranchStartDate to vToDate
Application enclosed for your reference.
Hi Manish,
I had a similar issue at a time.
- You have to generate a StartDate (either branchstartdate or vFrom, whichever is later) in the script.
- In a calendar, you can generate a field that will be 1 for Mo-Fri, 0.5 for Sat, 0 for Sunday
=> You can use a mapping_table to also get the holidays and assign a 0 value to them like you do for Sundays. (still on scripting level)
- Then you can quite easily sum up the Trading_day values you have between your [generated StartDate] and your vTo
HTH
Best regards,
DataNibbler
How do you know what Trading Days are related to which Branches? Your two tables are not linked in the .qvw file.
Try this Expression:
Sum(If(BranchStartDate>=vFromDate,
If( [Trading Date]>=BranchStartDate and [Trading Date]<=vToDate,TradingDay),
If( [Trading Date]>=vFromDate and [Trading Date]<=vToDate,TradingDay)))
This works in your application.
Cheers,
Vidyut