OVER function in cross table trouble

I have a cross table that has [Date] on the Vertical Axis and is binned by Month.  I'm trying to do a UniqueCount() on a field, but I want the aggregation to be per each individual day, not the month.  The data set has details of each product from various locations, but I just need the unique count of products per day summed for the month.  Something like UniqueCount([product]) OVER (Day([Date])).  But, this says the express is invalid.  I assume this is because of the in-db work so I need to use a THEN function.  Except, i'm either able to get what is effectively a count of all rows, or just a result of one when I test with just one product.

I assume I'm just not telling Spotfire how to correctly group by the day level, but everything I've  tried results in all rows or just one row.

UniqueCount([Product]) then Sum([Value]) OVER (LastNode([Axis.Rows]))

This seems correct, but it still gives me an answer of 1.  The current data set has 20 days of data for my test product, so the result should be 20.

UniqueCount([Product]) OVER (Intersect([theday]))

Also gives a result of 1.

I even did a calculated column of the Day() of the [Date] field to circumvent the whole date binning of Month vs Day, but that didn't help.  I still got an answer of 1.

There's just something about the OVER function or node functions that I'm just not grasping that's probably the answer here.

Attachments

AttachmentSize
Binary Data over_function_for_time.dxp189.11 KB

1 Comment

I'm trying to get this kind of result:

SUM(COUNT(DISTINCT Product) GROUP BY Date) GROUP BY Month

The attached file should help.  The answer for the top right cross tab should be 4.

I'm starting to think this just isn't possible.

Steve Fernandez - May 24, 2018 - 9:22am ::
+ Add a Comment

(1) Answer

Login