1 Reply Latest reply: Jan 27, 2017 1:47 PM by David Rudolph RSS

    Create a Dimension for MonthName(FirstOrderDate)

    Samuel Lin

      Hi Qlikkers,

       

      I have a data table that contains:

      [IDs]

      [OrderDate]

      [ProductID]

      [Revenue]

      [MarkettingChannel]

       

       

      I would like to create a pivot table that with the row dimension of MarkettingChnnel and the column dimension of the monthName of the first order date of an IDs

       

      Does anyone have an idea how to create a dimension of first order date based on IDs? and I would just use the monthName function to convert it to monthName?

       

      Goal: besides to know the number of IDs joined by month, I also would like to know the total revenue by channel and by firstOrderMonth.

       

      Many thanks,

      Samuel

        • Re: Create a Dimension for MonthName(FirstOrderDate)
          David Rudolph

          I would create the new dimension in the load script instead of in the chart itself.  You can either join the new dimension(s) back onto your original table or keep it as a separate table.  Hypothetically, if your original table was called FACTS, then the script for your new table might look something like:

           

          FirstOrders:

          LOAD *,

                    MonthName(FirstOrderDate) as FirstOrderMonth

          ;

          LOAD IDs,

                    Min(OrderDate) as FirstOrderDate

          Resident FACTS

          Group by IDs

          ;

           

          This should create another table keyed on the IDs field that will give you the date of each ID's first order and then a dimension field with the MonthName of that FIrstOrderDate, assuming that your OrderDate field contains quality date data.

           

          For your chart you would use your MarketingChannel field for your rows, the FirstOrderMonth for your columns, and then you can create whatever measures you would like.