7 Replies Latest reply: Aug 10, 2010 6:11 PM by John Witherspoon RSS

    Set Analysis calcuation

      I am working with v.9. I have a straight table that contains retail sales/revenue/COGS/Shipped cost data summed by week. On one column I want to get the shipped percent based upon the previous weeks COGS. I tried the formula below and only get Null values. What am I doing wrong?

      SUM( [Qty Shipped] * Cost )


      SUM( {$< [WE DateNum] = {$(=( [WE DateNum] - 7))} >} [Qty Sold] * Cost )

      I am not sure I completely understand Set Analysis so forgive me if this seems obvious.



        • Set Analysis calcuation

          Set analysis with date fields is a serious pain in the undercarriage.


          Try this (note the tiny addition of single quotes):


          SUM( [Qty Shipped] * Cost )


          SUM( {$< [WE DateNum] = {'$(=( [WE DateNum] - 7))'} >} [Qty Sold] * Cost )





            • Set Analysis calcuation

              Thanks for replying!

              It still doesn't work. If I break out just the last bit into an expression it is returning zero. Does it matter that the selection criteria allows the user to choose multiple weeks? I would think not, but thought I would mention it in case.

            • Set Analysis calcuation
              Mike Garciam

              If the week is the dimension of your table, you should try with the above() function. It will give you the value of any given formula, but evaluated on a previous row(s).

              Search the help for that function and you should fine everything you need.


                • Set Analysis calcuation

                  thanks for your suggestion!

                  Technically, I can use ABOVE(), however, this is assuming that the weeks shown are consecutive and puts the burden on the user to make sure to select one more week than what they actually want to view. I will use this as a last resort but would rather get the Set Analysis to work.

                    • Set Analysis calcuation
                      John Witherspoon

                      The formula returns null because a set is only built once for the entire table, not once per row of the table. So the WE DateNum field contains all possible or selected values at the time the set is built. You can't subtract 7 from a SET of values, so it returns null. No WE DateNums are null, so you're then doing set analysis with a null set. So that sum returns null, and thus the division of sums returns null.

                      You can verify it by selecting a single WE DateNum. If what I described is the ONLY problem, you'll get the correct result for that one week. But you may need to format the value in the set properly as well, so I suspect it still won't work.

                      In any case, I think the set analysis approach is the wrong solution since you can only get results for a single selection, and not for all weeks in the table.

                      I normally suggest building an As Of table in the script:

                      As of Week, Week Type, WE DateNum
                      08/09/10, This Week, 08/09/10
                      08/09/10, Last Week, 08/02/10
                      08/02/10, This Week, 08/02/10
                      08/02/10, Last Week, 07/26/10

                      You'd load that from your actual data, not inline like this, and you'd define your weeks however you want. Then you'd do this:

                      dimension = As of Week
                      expression = sum({<[Week Type]={'This Week'}>} [Qty Shipped]*Cost)
                      / sum({<[Week Type]={'Last Week'}>} [Qty Shipped]*Cost)

                      You could use 1/null flags instead of the week type if you prefer. The chart should load a little more quickly if you do.

                        • Set Analysis calcuation

                          Thanks John!

                          I think I understand. And yes, it works if I choose only one week (it adds a second row for the prior week and the only column without null is the one I am working on).

                          Once I create the table as you have suggested, won't it duplicate data for all of the regular summations? ie. SUM([Qty Shipped] * Cost) I assume this becasue there will be two rows where the WE DateNum will match against my sales data based upon on the link between WE DateNum. Did that make sense?


                            • Set Analysis calcuation
                              John Witherspoon

                              It won't duplicate all your regular summations UNLESS you swtich other tables to use As of Week. The WE DateNum is still connected to your Sales only once. It's only As of Week that is connected to each sale twice, and thus requires you to either use Week Type as a dimension, or to "select" a value for Week Type using set analysis to narrow it down to a single week. Of course, if you wanted a two week rolling average, say, the As Of Week would give that to you directly.

                              To be honest, I've never used this in any of my applications. Somehow I've just never needed to compare periods like this, even though it seems such an obvious and common requirement. So it's possible there are some lurking issues that would show up out in the real world that I'm not thinking of here. One obvious one, I suppose, is that you have two different week fields now instead of one, and they behave differently. I don't think that's a critical flaw, though, since you WANT different behavior. And I've suggested this many times on the forum in the past, and haven't heard complaints. Not that someone would come back to the forum three months later and say, "Well, I took your advice since it worked fine for a simple example, but it totally didn't work out in the real world, and here's why." So who knows. Looks good to me in simple examples, though. :)