Using conditional dates in stat definition

[COKE_MAN]

With the recent removal of rakeback on Merge, I want to edit my rakeback stat to calculate the old percentage before Feb 1 and either have 0 or whatever my new rate is within a date range. I was able to get it to work using "if(date_played< '2013/02/01 00:00:00', Crake* .35, 0)" That calculates things as I want however when I view this on a report or on a graph, it acts like it is grouping by date. For example if I want to just get the value for a date range (say a month), I could get just one row that was summed. With the above modification, the report will show a row for each date in the range. The graphs are messed up too. Plus the reports will not sum this at the bottom. Strange thing is if I modify this to show a running amt_won + rackback balance, adding the +amt_won column to the stat with no other modification makes this sum on the reports. Still gives a row per date though.

Is there a way to put a date based conditional in a stat without seeing this effect?

[WhiteRider]

Is "if(date_played< '2013/02/01 00:00:00', Crake* .35, 0)" your exact expression?
If you use "date_played" directly in an expression you're using another column, which will cause problems. What you need to do is use the same database field as the 'date_played' column in your new column.

e.g.

"if(datefix_fromutc_noadjust[ cash_hand_player_statistics.date_played ] < '2013/02/01 00:00:00', Crake* .35, 0)"

[COKE_MAN]

I'm not using that in a column definition, but in a stat definition. date_played is a column, CRake is a custom column. Both accessible from the stat definition. The expression works just fine in that the values are only calculated at 35% on days before 2/1/2013. The group by issue is the problem. Also worth noting that I did try pushing it down and building a custom column for the definition just as you mentioned and that does work too, but the group by problem still remains.

[WhiteRider]

Doing it in the statistic like that won't work. You need to do the date check in a column. If you have problems with that please post your column expression so that we can check that out too.

[COKE_MAN]

I am not sure what you are saying won't work. It does. it just groups by date when I use a date range in the filters and shows a row for each date when I expect a summed, single row.

Weird thing though, I just redid it in the column and it now returns a single row like I want. I have been messing with this for a couple of days and moving the logic between the 2 and it always gave me multiple rows even when moving it to the column definitions.

Here are examples of both methods:

Definition in stat:
stat value expression - if(date_played<'20113/02/01 00:00:00',Crake*.35,0)
stat format expression - format_money(if(date_played<'20113/02/01 00:00:00',Crake*.35,0),true)
date_played column definition - dateextract_date[ datefix_fromutc[ cash_hand_player_statistics.date_played ]]
Crake column definition - sum(if[cash_hand_player_statistics.amt_bet_ttl > 0 AND cash_hand_summary.amt_rake > 0, cash_hand_summary.amt_rake * (cash_hand_player_statistics.amt_bet_ttl / cash_hand_summary.amt_pot), 0])

Definition if used in column:
Crake definition - sum(if[cash_hand_player_statistics.amt_bet_ttl > 0 AND cash_hand_summary.amt_rake > 0 AND cash_hand_player_statistics.date_played <'2013-02-01 00:00:00', cash_hand_summary.amt_rake * (cash_hand_player_statistics.amt_bet_ttl / cash_hand_summary.amt_pot), 0])

The issue now is that with the logic in the column definition, it incorrectly zeroes out Jan 31. Putting the logic in the stat instead will give me a value for Jan 31.

[COKE_MAN]

It dawned on me that putting the conditional in the column like I am does not do the UTC adjustment. That is probably why I am not seeing anything for Jan 31 there, but I do if I put it in the stat as the date_played column has that adjustment in its definition. I'll give it a try when I get a chance

[COKE_MAN]

BTW, putting "if(datefix_fromutc_noadjust[ cash_hand_player_statistics.date_played ] < '2013/02/01 00:00:00', Crake* .35, 0)" in a column expression returns Invalid SQL when validating.

[COKE_MAN]

Meh, can't put the datefix function in a column definition for some reason. Like I said above, it returns an invalid SQL error. So I can get the correct dates by using a stat definition, or I can put the base database column in another column definition and be off a date. A hacky way around it would be to just use 2/2/2013, but I would rather have the correct syntax in there.

[COKE_MAN]

Bump for WR.

Can you verify what you were saying about the column definition? What I see here when working in the column definition screen is as follows:
-datefix_fromutc_noadjust[ cash_hand_player_statistics.date_played ] alone works
-if[datefix_fromutc_noadjust[ cash_hand_player_statistics.date_played ] < '2013/02/01 00:00:00',....] fails even when just putting constants in there like 0 or 1 for the true/false parts
-Referencing Crake (another derived column) in the column definition fails. Even if doing it without a conditional. So simply using Crake *.35 in a column definition fails.

So i am still in the situation I had before. Logic in stat works, but makes the reports and graph group by date and give multiple rows. Logic in column returns one row, but the dates are off by 1. Cannot reference a derived column in another column, (although I have examples in my DB that do this)

Workaround is to just put the database field cash_hand_player_statistics.date_played directly in the column definition using the conditional offset by one day and rebuild Crake in every column where I need that info.

For example to get a RB stat that calculates differently before 2/1/2013:

Instead of:
if[datefix_fromutc_noadjust[ cash_hand_player_statistics.date_played ] < '2013/02/01 00:00:00', Crake *.35,0]

I have to use:
sum(if[cash_hand_player_statistics.amt_bet_ttl > 0 AND cash_hand_summary.amt_rake > 0 AND cash_hand_player_statistics.date_played < '2013-02-02 00:00:00', cash_hand_summary.amt_rake * (cash_hand_player_statistics.amt_bet_ttl / cash_hand_summary.amt_pot) * .35, 0])

Or just put the date logic in the stats and deal with weird reports

This is not the only place I use the Crake calculation. That's why I am spending so much time on it. Otherwise, I would just deal with it. Are nested IF statements supported? That would help me in this area too.

[WhiteRider]

Using columns directly in other columns will cause weird results. A "normal" column sums up an expression across all hands, and then if you use it in another column which is again summing up results across all hands it won't work correctly.

The last expression you quoted is how you need to do it.

You should be able to nest IF statements, although I don't see what benefit that would have here?