How can I make this summary?

[rcandeo]

Hi, I have an aplication that shows the sales by customer month by month. Any month is an expression using set analysis. The last two expressions are the grand total and the share comparing all the customers.

The lay-out is something like this:

Customer        jan/13     feb/13     mar/13     ...  dec/13     total     share

Ryan               50          20           40                30           350      40%

Paul                30          25           18                20           200      20%

.

.

John                5       3         2                 8       42        5%

What I wanna is to show the comparison between two years like this:

Customer          jan/12     jan/13     feb/12     feb/13     ...     total/12     total/13     share/12 share/13

If i put the year and month in the dimension, I can move them to the top right and get the month/year comparison without any problem, but how about my grand total and share? Any idea please?

[nilesh_gangurde]

Hiii,

You can add separate Expression for each month and total and share.

then it will be easy to add total as wel as share.

then at the above situation you will have 8 expressions(i,e. Each month will have one,totals will have 2 ,and share will have 2 ) with dimension Customer.

-Nilesh

[rcandeo]

OK, I don´t know if I understood you right, but I wanna total and share only at the end, not for any month.

[nilesh_gangurde]

yes u will achiev the same with above suggestion.

[nilesh_gangurde · Accepted solution]

to calculate month by month data ypu will add the Month field into dimention,

instead of that you only add customer in dimension, and write the expressrion like this.

1. Sum({<Monthyear = {"jan 2012"}>}Sales)
2. Sum({<Monthyear = {"jan 2013"}>}Sales)
3. Sum({<Monthyear = {"feb 2012"}>}Sales)
4. Sum({<Monthyear = {"feb 2013"}>}Sales)

then for total

1. Sum({<year = {"2012"}>}Sales)
2. Sum({<year = {"2013"}>}Sales)

and the same way for shares.

-Nilesh

[rcandeo]

I´m getting this:

                       jan                                                                  feb

Customer        2012                           2013                             2012                                   2013

Customer       Movto Total Share      Movto Total Share      Movto Total Share              Movto Total Share

Ryan               40        40     0.05%     50         50      0.06%    50          60     0.04%

Paul                20        20     0.04%     30         30      0.04%    60          40     0.03%

And I wanna

                       jan                          feb

Customer        2012     2013       2012   2013      2012     2013       2012     2013

Customer       Movto    Movto    Movto Movto  Total     Total     Share  Share

Ryan               40         50           50      40         90          90         0.06%    0.04%

Paul                20         30           30      30         50           60        0.04%    0.03%

[rcandeo]

That´s the point. I had done this way with only one year, but my user wanna choose if he wants to see only the data for one year, or the comparison between two years.

So if I put the year and the month in the dimension I don´t need to create so many expressions, but how can I make the summary and the share?

[kaushiknsolanki]

Hi,

     I understand what you are looking for, but when you use the dimention as Year and Month, its not possible to get the summary(Total) at the end.

     So the work around if what Mr. Nilesh told. You create a seperate expression for each of the Month, and at the end you can create the expression for Summary and Share.

     But yes if you adopt this way you will need to write all the expressions manually. But you can try to make the expressions bit intelligent. Meaning you can script then such a way that they change according to user selections.

Regards,

Kaushik Solanki