8 Replies Latest reply on Dec 9, 2011 4:09 PM by Jon Mitten

    Count sale type by format type

    Jon Mitten



      I'm building a book store analysis tool, and one problem I'm having is being able to break up our sales records by sales type and book format type.  So, for example, I want to be able to count the number of sales of a particular book - say Girl With the Dragon Tattoo. Instead of the ISBN, there's an internal ID system. So, Girl With the Dragon Tattoo has a book_id = 11107. It's being sold at 4 bookstores around town, Barnes & Noble, Elliot Bay Books, Third Place Books, and Half Price Books. Each sale type has its own id, where ID = 1 represents sale at cover price, ID = 2 means a sale at a promotional price, where a coupon or special offer is involved, and sale_type_id = 3 is a clearance price. Then, of course, there are different formats of the book, being hard-cover, soft-cover, large-print for reduced-vision readers, and Braille for non-sighted readers, with format_type_ids of 1, 2, 3, and 4, respectively.


      I want to be able to count the numbers of sales by each sale type and format type, so to be able to get the statistics by vendor name, book title or publisher, where the sale type was full-price (sale_type_id ID = 1) and the format type is hard-cover (format_type_id = 1), and so on.


      How can I count each type grouped as such, broken out into individual pills like "Full Price Hard Cover sales", "Clearance Large Format Sales", and so on?

        • 1. Re: Count sale type by format type
          Jon Mitten

          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)

          • 2. Re: Count sale type by format type
            Jon Mitten

            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.



            • 3. Re: Count sale type by format type
              Jon Mitten

              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.

              • 4. Re: Count sale type by format type
                Jon Mitten

                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)

                • 5. Re: Count sale type by format type
                  Jon Mitten

                  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.

                  • 6. Re: Count sale type by format type
                    Richard Leeke

                    Have you considered creating a group?

                    • 7. Re: Count sale type by format type
                      Jon Mitten

                      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.

                      • 8. Re: Count sale type by format type
                        Richard Leeke

                        If you can post a sample workbook which illustrates the issue someone can take a look.