3 Replies Latest reply: Aug 12, 2011 3:03 PM by Guillermo Rossney RSS

    Help with Dimensions & Expresions on a Pivot Table

      Hello!

      First, i must say i'm a newbie with QV. Sorry if i can't explain myself in an appropiate way.

      In addition, i'm not completely good with English. I'm from Argentina. What a challenge for you all!

       

      I'll try to explain what i'm trying to do:

       

      I have a table with orders.. it means:

      a customer id, an order_date, an order_id, a product_id, quantity, etc.. for a couple of years.

      In addition, i have a calendar vinculated..

      In the calendar, i can select just one year, and just one month. One period.

       

      First, i wanna clasificate customers in 2 groups:

      old customers: those who has orders in the last six months (depending on the month/year selected)

      "new" customers: those who hasnt orders in the last six months (depending on the month/year selected)

       

      Then, what i'm trying to do is a pivot table with, for each day in the month, how many diferent customers have placed the first order in the month (in other words, how many customers has been "activated" in that day of the month. Just the first order of a customer in the month counts as an activation), separated by Old and New customers.

       

      Something like that.

       

       

      Type
      01-Mar-201102-Mar-201103-Mar-201104-Mar-201105-Mar-2011
      Old Customers103201156187133
      New Customers1513171218

       

       

      I made an expression to make the first classification (old vs new, depending on mon/year selected), but i can't use it as a calculated dimension (it gives me an error). I don't know if make an expression is the best approach for that... And later i have to solve not only if is old or new regarding the previous 6 months of the period selected, i have also to clasificate if there are activations or not for that day in the current period.

       

      Could you please send some suggestions to help me out?

      Samples, how-to's, etc.. any comment will be appreciated!

       

      Thanks in advance.