Calculating % splits in dimensions and expressions

[burgersurfer]

Got a question on pivot tables and handling calculations:

I want to analyse sales over stores and product categories, viewing % sales per category, Value of sales over a period, and compare that to the same previous period.

If I use a pivot table with only 1 dimension (store) and add 3 expressions for each category (Category 1:%sales, Value and Last Year Category 2:%sales, Value and Last Year etc), but this "hard-codes" my categories to the columns defined.

If I use 2 dimensions (store to the left, and category above), I only generate 3 expressions that QV will analyse by category and this can change when categories grow.. BUT how are the calculations done? I need to tell QV to give me a category % of the total on Column 1, and only give me sales per BU in column 2.

Then also need to know how the previous period can be indicated.

In excel, it looks like this:

[stephencredmond · Accepted solution]

Hi,

You will have 3 expressions. The first 2 I can tell you easy:

Value = Sum(Value) // assuming your Value field is called Value.

% = Sum(Value)/Sum(Total <Store> Value) // Total takes out the split across category and gives you the Store total.

Prev. Period = Sum({ A Set Goes Here } Value)

I can't give you the correct Set because I don't know anything about how your periods are worked out. Have a search around the site here and see if you can find something that help.

Regards,

Stephen

[burgersurfer]

Thanks for the reply Stephen

I am using the SUm(Value) formula for the first expression, thanks -

Need a bit more explanation on the % calc though - <Store> should be the store key as I understand. WIth my data this is Store_No. Should the formula then be:

Sum (Transaction_Detail) / Sum (Total <"Store_No"> Transaction_Detail)

I get no results with this?

[stephencredmond]

Hi,

Not necessarily the Id - use the field that you use in the actual chart. In my example, Store holds the name of the store.

Regards,

Stephen

[burgersurfer]

Thanks Stephen, this looks like a sensible result

For the dates, I used a calendar generator found in another thread. This leaves me with a set of dates from 2006 to current, in options including CalendarDate, CalendarYear, CalendarMonth, ..Day etc.

I want the option to select a time period (day, week, month or year) and get the sales data as above for that period, + a figure for the comparative previous period. So If the user selects year, the previous year should be calculated, or if he selects quarter, the same quarter last year should be calculated

Makes sense?

[stephencredmond]

Hi,

If you know what your earliest date you have and the latest then you can calculate what those values are -1 year - then use that calculation in a Set.

Lots of examples on here.

Stephen