Loading data grouping for particular value of a dimension

[#1]

Hi,

I have a Orders table, stored in a QVD and to load, like this:

OrderID, OrderDescription, TotalOrderAmount, OrderYear, OrderMonth, MonthlyOrderedAmount.

An order can have one or more monthly lines for year. In the table, the primary key is composed of OrderID, OrderYear and OrderMonth.

I want to load the orders to have the monthly OrderedAmount on columns to show them in a tabular format. It is important to show the OrderID, the OrderDescription and the TotalOrderAmount. This last amount is repeated for each monthly order row.

I think to use this code:

(pre-load into resident table Orders)

OrdersByMonth:

LOAD DISTINCT OrderID, OrderYear, OrderMonth, OrderDescription, TotalOrderAmount RESIDENT Orders;

INNER KEEP

LOAD OrderID, OrderYear, OrderMonth, SUM(MonthlyOrderedAmount) as JanuaryOrderedAmount RESIDENT Order WHERE OrderMonth = 1 GROUP BY OrderID, OrderYear, OrderMonth;

INNER KEEP

LOAD OrderID, OrderYear, OrderMonth, SUM(MonthlyOrderedAmount) as FebruaryOrderedAmount RESIDENT Order WHERE OrderMonth = 2 GROUP BY OrderID, OrderYear, OrderMonth;

INNER KEEP

LOAD OrderID, OrderYear, OrderMonth, SUM(MonthlyOrderedAmount) as MarchOrderedAmount RESIDENT Order WHERE OrderMonth = 3 GROUP BY OrderID, OrderYear, OrderMonth;

INNER KEEP ... and so on ...

Is this a good solution or is there a better one? Any helps to me, please?

Many thanks

[stevedark]

Hi there,

It sounds to me that what you need is simply a Pivot table based on your month.

To do this go from your initial data set and add a new Chart. Select to display this in a Pivot view. Set the dimensions to be OrderDescription, OrderYear and OrderMonth. Add a single expression of MonthlyOrderedAmount. On the properties page check the values for Allow Pivotting and Always Fully Expanded.

Once your chart has been rendered you can drag the year to the top of the page (click and hold the label to do this). Then do the same for the Month field to drag it under the Year field.

If you like you can put totals in by selecting the Properties tab and clicking the Show Partial Sums box for both Year and Month dimensions.

You will probably want to have better formatted date dimensions, and this should be done in your initial load before writing to QVD, with something like this in your load script:

OrderYear as [Order Year],
Month(MakeDate(OrderYear, OrderMonth, 1)) as [Order Month],
Date(MakeDate(OrderYear, OrderMonth, 1), 'MMM-YY') as [Order Month Year],

Formatting the dimensions like this will make it easier to get the legends you want when you build more charts. Having a combined month year can be particularly useful.

Hope that helps,

Steve

[#3]

Hi Steve, thanks for your reply. Also I think to use a pivot table, but I have some doubts.

I need to show also OrderID, so the dimensions should be 1) OrderID, 2) OrderDescription, 3) OrderYear and 4) OrderMonth.

I need to show also the TotalOrderAmount: it isn't the sum of the monthly amount but it's an expected amount. Moreover I could need to show the monthly invoiced amount.

Now, in this case could a table box be better than a pivot chart?

Thanks

[stevedark]

Hi there,

You can simply add the OrderID as one of the dimensions of the pivot, this will not cause any issues. You can also have two expressions in your pivot - the sum of the monthly amount and the sum of the total amount. I would imagine however that if you are including the TotalOrderAmount you will need to be very careful what partial sums you include - as you would not want to double count totals across months.

Regards,
Steve

[#5]

Hi.

You say me that with pivot chart I'm free to add other descriptive attributes that I must manage as a dimension, isn'it?

But if I use a pivot chart it couldn't be necessary to use a LOAD with JOIN to calculate the monthly order amounts (see my posted code), isn't it?

The TotalOrderAmount is an expected amount, but the MonthlyOrderAmount is an actual amount.

Thanks

[#6]

Hi,

I have tried to use the pivot chart with the expanded dimensions. In the char the drag & drop is allowed but I cannot to move the Year dimension on the top. I want to show the monthy order amount on the columns:

1st col ... 2nd column ............. 3rd column ................ 4th col ... 5th column ..................... 6th column ....................... 7th column ...............................

OrderID ... OrderDescription ... TotalOrderAmount ... Year ....... JanuaryOrderAmount ... FebruaryOrderAmount ... MarchOrderAmount and so on

The representation should be like an Excel spreadsheet.

Thanks

[stevedark]

Hi there,

From your further explanation it sounds like TotalOrderAmount should be another dimension, rather than an expression. It also sounds that Year does not need to be dragged about, but month does. You need to ensure that Allow Pivoting is selected on the Properties tab. Dragging dimensions can sometimes be fiddly getting things to anchor in the right place. As you drag the month you need to wait for a horizontal blue line to appear before letting go of your left mouse button.

If you upload an example of where you are at presently and an Excel representation of where you are trying to get to I can take a look at this for you.

Regards,
Steve

[#8]

Hi,

yes TotalOrderAmount for me is a dimension in the chart.

I'me able to place year and month dimensions in the right positions but after eight dimensions when I add a dimension it goes below year and month and I cannot move it correctly.

I attach an img. See Importo contratto below Anno (or Year in English) and Mese (or Month in English). Thanks

P.S.: no data are showed for the selections.

[stevedark]

Hi,

You should still be able to drag dimensions even with nine dimensions. Sometimes though it is neccesary to change the order of dimensions by using the Promote and Demote buttons on the Expressions tab of the chart's properties to get things in the right order.

Regards,
Steve

[#10]

Hi,

I'm not able to move Importo Contratto dimension in the right position also even playing with the order in the Dimensions tab.

Thanks