Actually, even more relevant to my needs , how would I be able to get both hard- and soft-cover sales by full-price.
I want to do a calculated field that's essentially this:
IIF([format_type_id] IN (1,2) AND [sales_type_id] = 1, 1, 0)
However, I cannot find the calculation comparison operators defined for an "IN" operator. There are greater-than, less-than ranges, but I am concerned more about non-sequential identifiers, or even textual identifiers, like:
IIF([format_type_id] IN ('hard-cover', 'soft-cover') AND [sales_type] = 'full-price', 1, 0)
Sure -although I haven't been able to implement the count that I want, I have tried to, and have a Hard- and Soft-cover Sales Calculated Field... I want to be able to put in a range of values (SQL equivalent of IN()) - that's really all I need to know how to do. I've managed to answer my other questions. I just want to be able to do it within a calculated field. It'd help if there was a full description of the available comparison operators within calculated fields.
book_sales.twbx 27.3 KB
Well, I would consider the Passthrough function to be less than ideal. I don't want to have to fuss with raw sql for such a (seemingly) simple operation. I also don't want to have to type out every value as an OR , which seems to be the only way to achieve my goal.
Unfortunately, my real-world scenario includes hundreds of formats and hundreds of book-stores and hundreds of publishers and titles.
Basically, I'd like to do this:
IIF( [encode_type_id IN (98,99,100,101,102,103,104,105,106,107,108,109,136,140,146,184,235,257,295,296,338,339) AND ([status_code] = 200), 1, 0)
Instead of this:
IIF(((encode_type_id = 98) OR (encode_type_id = 99) OR (encode_type_id = 100) OR (encode_type_id = 101) OR (encode_type_id = 102) OR (encode_type_id = 103) OR (encode_type_id = 104) OR (encode_type_id = 105) OR (encode_type_id = 106) OR (encode_type_id = 107) OR (encode_type_id = 108) OR (encode_type_id = 109) OR (encode_type_id = 136) OR (encode_type_id = 140) OR (encode_type_id = 146) OR (encode_type_id = 184) OR (encode_type_id = 235) OR (encode_type_id = 257) OR (encode_type_id = 295) OR (encode_type_id = 296) OR (encode_type_id = 338) OR (encode_type_id = 339) AND status_code = 200), 1, 0)
To make matters worse, the only example I can find is not appropriate to my issue. Searching for RAWSQL_INT produces a validation error in these forums. Furthermore, using the passthrough function RAWSQL_INT like this:
RAWSQL_INT("SELECT COUNT(%1) ...",[Full Price Sales)])
produces a calculation error of type: Unknown function RAWSQL_INT called.
I'm not using a data extract - this is live from the postgresql database.
Following the instructions for grouping my rows, the Group button (paper clip) is disabled.
This could be because I'm defining a sale as IIF(sale_type_id > 0, 1, 0), because inventory with no sales type or a sales type of 0 indicates a loss of inventory due to theft, foreclosure, destruction, disposal, or in a buy-1-get-1 scenario where the book is not actually sold. I want this formula to ignore those cases, though those cases exists in my actual data base.
If you can post a sample workbook which illustrates the issue someone can take a look.