AnsweredAssumed Answered

Filter SUM calculation in portal

Question asked by ultranix on Jan 17, 2019
Latest reply on Jan 17, 2019 by ultranix

Hey, I am trying to create a trading analysis solution, where I could spot the best time of the day, best play, best setup, best side, and so on.

 

I have ORDER table, in which I place all the individual orders (it's like Invoices, with no Lines table, because one order is one order, in of itself).

 

I also created TIME table, where I stored 26 time entries, starting at 09:30, then 09:45, 10:00, 10:15, all with 15 min increments up to 15:45.

I also have global fields of Monday, Tuesday, Wednesday, Thursday and Friday - weekday names.

 

Then I created joined tables: ORDER_Time_1 (TIME::time from <= ORDER_Time_1::open time AND TIME::time to > ORDER_Time_1::open time AND TIME::Monday = ORDER_Time_1::day_name) for Monday trades, ORDER_Time_2 for Tuesdays trades, ORDER_Time_3 for Wednesdays trades, ORDER_Time_4 for Thursdays trades and ORDER_Time_5 for Fridays trades.

 

Then in TIME table I added calculations win_1 (Sum(ORDER_Time_1::win), win_2, win_3, win_4, win_5 for sum of winning trades each day.

 

I created a portal to display all those records from TIME in layout based on USER table and up to this point everything works like a charm - I have all the times of the day in 15 min fractions and by each of them I see Monday's, Tuesday's, Wednesday's, Thursday's, and Friday's sum of all winning trades.

 

The problems occur, when I want to have additional filtering, i.e. if I add a field USER::xtimeframe (e.g. 5 min.), in which I would like to calculate totals for each day of those trades taken from 5 min timeframe, I don't know how to do it.

 

And, if there's only one layer of filtering, I could include IF based calculations in w_1, w_2 etc. fields for it to calculate the right thing, but as I have 3 layers of filtering, I have 24 different variations, and I don't want to write a calculation for each variation.

 

So how can I achieve this? (preferably, keeping it in portal and not use a list view to achieve similar thing).

Outcomes