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

[#2]

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.