How to calculate totals for two dimensions?

[#1]

I have monthly $$ data by industry.  Ultimate goal is to determine top 25 change in percent of total $$, month over month.

So - first figure out industry % of total $ this month, then compare that to industry % of total $ last month.

(Industry $ this month / Total $ this month) - (Industry $ last month / Total $ last month)

The data looks like this:

Industry	Period	$$
ABC	Jan 2014	100
ABC	Feb 2014	120
ABC	Mar 2014	110
DEF	Jan 2014	50
DEF	Feb 2014	30
DEF	Mar 2014	80
GHI	Jan 2014	200
GHI	Feb 2014	219
GHI	Mar 2014	201

So for Industry ABC:

Feb would be 120/369 = 32.5% (369 is total $ for Feb across industries)

Mar would be 110/391 = 28.1%

28.1% - 32.5% = -4.4% month over month change

Do the same for DEF and GHI, then sort with highest month over month change first.

Ultimately I'd like to show this in a trellis bar chart, with a chart for each industry, $ on the y axis, month on the x axis.

[#2]

Also - the $$ are summed to get an industry total.  So I'm already calculating SUM($$) to get to the data you see above.  I've tried playing with AGGR and TOTAL with no luck.....

[#3]

Tina,

you like to select always one month (current) and calculate MOM with previous month of selected, or maybe you prefer to look for top 25 taking into account more months?

[#4]

We will ultimately look at year over year and quarter over quarter as well, going back many months.  So yes, we would account for more months.

[#5]

Yes, but many on one chart, or rather chart will be prepared when user selected one month (for example 02-2014) and then you will look for top 25 between 01-2014 and 02-2014?

[#6]

Ahh - sorry!  I didn't follow your question. The chart will be prepared based on selecting just one month and then looking backwards from there.  Most often we would base this on the current month.

[fernando_tonial]

hi, see the qvw file.

Sum(Amount) / Sum(TOTAL <Period> Amount)

and

Sum(Amount) / Sum(TOTAL <Period> Amount) - Above(Sum(Amount) / Sum(TOTAL <Period> Amount))

Kind Regards.

Tonial.

[#8]

So,

if you alvays interested in only two months

selected month and selected month - 1 - it is easy with set analysis.

I will try to give you some ideas, but not today .... I have to leave now

regards

Darek

[#9]

Aha!  Thanks Fernando - this worked.

The final step is to sort the results based on % chg MOM in descending order, because I only want the top 25 % chg in my trellis chart.

I went into the Sort tab, and for the Industry dimension I checked "Expression" and put in the calc for the % chg from prior month.  Not seeing results though - thoughts?

Thanks again - much appreciated.

[aveeeeeee7en]

Hi

See the Attachment

Regards

Aviral