Square root

[Dan_Jazz_Man]

No, there was a mistake in the earlier post, it should read "cnt_holecard" every where.

[kraada]

That's really odd. What section are you doing this in? Holdem Cash Hand or Holdem Cash Player Statistics?

Could you also paste the cnt_holecard definition we could see how that one is set up?

[Dan_Jazz_Man]

The definition of cnt_holecard is:
count(holdem_hand_player_statistics.id_holecard)
Both cnt_holecard and cnt_holecard_sqrt columns are in the Holdem Cash Hand.

If you remember, I tried the following expression for the cnt_holecard_sqrt column:
cnt_holecard ^ 0.5
Weird, it gives a "not valid SQL statement" warning, but works fine if I write:
cnt_holecard ^ 2

But guess what, I made it work!!! I defined cnt_holecard_sqrt as "sqrt(cnt_holecard)"! I think, WhiteRider wrote that there's no sqrt function available, but it appears that there is. I can only use it in the Column tab though, sqrt doesn't work in the Statistic tab.

Now, I'd really appreciate it if you could help me out with the report itself. It is about pocket pairs improving on the flop. I have an "Expected PP Hit Flop" statistic that is defined like this:
format ('{1}, ({2}), {3}',
format_number (cnt_holecard * 0.1175 - 2 * cnt_holecard_sqrt * 0.322, 0, false, false),
format_number (cnt_holecard * 0.1175, 0, false, false),
format_number (cnt_holecard * 0.1175 + 2 * cnt_holecards_sqrt * 0.322, 0, false, false) )
where 0.1175 is the probability that a PP improves on the flop, 0.322 is the standard deviation for 0.1175, so
cnt_holecard * 0.1175 represents the expected value,
(cnt_holecard * 0.1175 - 2 * cnt_holecard_sqrt * 0.322) and (cnt_holecard * 0.1175 + 2 * cnt_holecards_sqrt * 0.322) represent the left and right borders of the 95 % confidence interval.
"Format summary" expression for this statistics is the same. The problem is, the report calculates the expected value and the confidence interval correctly for any given pair, but I can't get it right for the summary. The "total" expected value is calculated correctly, but the confidence interval is not. If the summary has a type "sum", then it obviously just adds up the values for the borders of the interval, if it has the type "copy", then it shows up as interval borders for one pair. How do I feed the "total" cnt_holecard value (that shows in the summary) in the "format_number (cnt_holecard * 0.1175 - 2 * cnt_holecard_sqrt * 0.322, 0, false, false)" expression to get it right?

P.S. Maybe I could PM one of you guys with the attached report, if it's going to be easier to figure out what's going on?

[kraada]

Well, it's good to find out that sqrt() works!

We'll definitely keep that in mind in case anybody else would like to use it.

One quick thing that pops into my mind for your total value, would be to create another (sorry!) column, cnt_holecard_total and define it as sum(cnt_holecard). Then that value will just be the total of cnt_holecard and you can use it to get the confidence interval information you'd like.

And once you get this completely working, please add it to the repository, we would appreciate it.

[Dan_Jazz_Man]

Defining a new cnt_holdecard_total column as "sum(cnt_holecard)" results in the same "not valid SQL statement" warning.

[WhiteRider]

I think you should be defining your cnt_holecard_sqrt column as:
sqrt( count(holdem_hand_player_statistics.id_holecard) )
..rather than trying to define one column in terms of another.

FWIW, I'd like to see a way to do what you are trying to do, but at the moment it won't work that way.

[Dan_Jazz_Man]

Frustrating... I can't make a simple report

[Dan_Jazz_Man]

So, as I understand it, there's no point even in writing
format ('{1}, ({2}), {3}',
format_number (cnt_holecard * 0.1175 - 2 * cnt_holecard_sqrt * 0.322, 0, false, false),
format_number (cnt_holecard * 0.1175, 0, false, false),
format_number (cnt_holecard * 0.1175 + 2 * cnt_holecards_sqrt * 0.322, 0, false, false) )
for the "Summary format" expression, since the values are not calculated using these formulas anyway, but
instead are calculated as a sum of individual values for each pocket pair. Doesn't make sense of course, but you say there's
nothing to be done?

[kraada]

For the summary expression? You could try changing the summary type for the column you're using. Perhaps a different one will give you the result you're looking for?

[Dan_Jazz_Man]

OK, let me try to explain it again:
I have an "Expected PP Hit Flop" statistic. Its main expression is defined like this:
format ('{1}, ({2}), {3}',
format_number (cnt_holecard * 0.1175 - 2 * cnt_holecard_sqrt * 0.322, 0, false, false),
format_number (cnt_holecard * 0.1175, 0, false, false),
format_number (cnt_holecard * 0.1175 + 2 * cnt_holecard_sqrt * 0.322, 0, false, false) )

cnt_holecard column is defined like this: count(holdem_hand_player_statistics.id_holecard)
and it counts the number of times we have a specific pocket pair, it has summary type "sum" to show the total number of pocket pairs in the summary line. Obviously, I can't change its summary type to anything else.

cnt_holecard_sqrt column is defined like this: sqrt( count(holdem_hand_player_statistics.id_holecard) ).
I can change its summary type between "sum" and "copy", but neither make sense as you will see below. (I don't know what "append" does but it also doesn't give the correct result, nor do the other summary types).

Now, the problem is that I can't feed the cnt_holecard total value (the one that shows in the summary line for this column) and the cnt_holecard_sqrt total value (which is derived from it) into the summary expression. If I define the summary expression exactly the same:
format ('{1}, ({2}), {3}',
format_number (cnt_holecard * 0.1175 - 2 * cnt_holecard_sqrt * 0.322, 0, false, false),
format_number (cnt_holecard * 0.1175, 0, false, false),
format_number (cnt_holecard * 0.1175 + 2 * cnt_holecard_sqrt * 0.322, 0, false, false) )
then the values of cnt_holecard and cnt_holecard_sqrt are defined by whatever type of summary we have for cnt_holecard and cnt_holecard_sqrt.
It's not a problem with cnt_holecard, since instead of "cnt_holecard (AA+KK+ ... +22) * 0.1175" that we'd get if the cnt_holecard total value were used, we get "cnt_holecard (AA) * 0.1175 + cnt_holecard(KK) * 0.1175 + ... + cnt_holecard(22) * 0.1175. (Note that the notation is just to make things clear, it's not valid SQL, of course). In other words, instead of "cnt_holecard (value from the summary line) * 0.1175" we get the sum of "cnt_holecard (single pocket pair) * 0.1175", since cnt_holecard column has the summary type of "sum". It's fine, because by the law of multiplying out brackets the result is going to be the same (ignoring rounding errors).
The problem is with cnt_holecard_sqrt. We want to calculate "2 * 0.322 * cnt_holecard_sqrt" for the summary, or "2 * 0.322 * sqrt (cnt_holecard (AA+KK+ ... + 22))", but depending on the type of summary cnt_holecard_sqrt has, we get:
"2 * 0.322 * sqrt (cnt_holecard(AA)) + 2 * 0.322 * sqrt (cnt_holecard(KK)) + ... + 2 * 0.322 * sqrt (cnt_holecard(22))" - if the summary type is "sum";
"2 * 0.322 * sqrt (cnt_holecard (some pair)" - if the summary type is "copy";
Both of these results are incorrect of course.
The only workaround I see now is to duplicate cnt_holecard and cnt_holecard_sqrt columns, name them cnt_holecard_total and cnt_holecard_sqrt_total and try using them in the summary expression. Not sure, if it's going to work though.

I hope, I've cleared the situation up a bit, but then again, maybe I could PM you with the report to make it even more clear?