2 Replies Latest reply on Oct 22, 2014 8:15 AM by Jessica Lee

    Calculating total

    Jessica Lee

      Hi all,

       

      I have created a calculated field which is outlooking  # of tickets coming in by each customer.

       

      If attr([customer_nbr]) = '12345' and attr([month]) < 7 then zn([total_tickets_2014])* 0.2

      elseif attr([customer_nbr]) = '12345' and attr([month]) >= 7 then zn([total_tickets_2014])*0.3

      elseif attr([customer_nbr]) = '56789' and attr([month]) < 6  then 0

      elseif attr([customer_nbr]) = '56789' and attr([month]) >= 6 and attr([month]) <= 7  then zn([total_tickets_2014])*0.03

      elseif attr([customer_nbr]) = '56789' and attr([month]) >= 8 and attr([month]) <= 11  then zn([total_tickets_2014])*0.08

      elseif attr([customer_nbr]) = '56789' and attr([month]) =12 then zn(total_tickets_2014])*0.2

      elseif attr([customer_nbr]) = '24680' then zn([total_tickets_2014]])*0.1

      else 0

       

      This gives me the projected ticket count for each customer at the individual customer level.

      BUT when I remove 'customer_nbr' from the row to see the total projected ticket count per month, it doesn't give me the total number.

      It returns blank.

       

      What would be the reason here? is it attr() function that I am using in the calculation?

       

      Month is not date/time data, it's an integer dimension.

        • 1. Re: Calculating total
          Shawn Wallwork

          Is it returning blank or 0? When you take customer_nbr out of the viz then none of your ELSEIF statements can evaluate to TRUE, so I would have expected it to fall down and out through that final ELSE branch and return zero. But either way that calc won't give you the totals without that field being somewhere in the viz.

           

          I assume you're aggregating this because the [total_tickets_2014] field is an aggregate, right? I suspect there are other ways to work this out. If you post a package workbook with some sample data we'll be able to better help you.

           

          Cheers,

           

          --Shawn

          • 2. Re: Calculating total
            Jessica Lee

            Even with customer_nbr in the viz, it still returns 0 as grand total.

            And yes that's correct , the [total_tickets_2014] is an aggregate.

            I will upload sample dataset in 30 minutes.