topic Re: How can I make this summary? in QlikView

How can I make this summary?

rcandeo — Fri, 26 Apr 2013 18:10:21 GMT

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?

Re: How can I make this summary?

nilesh_gangurde — Fri, 26 Apr 2013 18:35:37 GMT

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

Re: How can I make this summary?

rcandeo — Fri, 26 Apr 2013 18:45:40 GMT

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

Re: How can I make this summary?

nilesh_gangurde — Fri, 26 Apr 2013 18:56:20 GMT

yes u will achiev the same with above suggestion.

Re: How can I make this summary?

nilesh_gangurde — Fri, 26 Apr 2013 19:02:29 GMT

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.

Sum({<Monthyear = {"jan 2012"}>}Sales)
Sum({<Monthyear = {"jan 2013"}>}Sales)
Sum({<Monthyear = {"feb 2012"}>}Sales)
Sum({<Monthyear = {"feb 2013"}>}Sales)

then for total

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

and the same way for shares.

-Nilesh

Re: How can I make this summary?

rcandeo — Fri, 26 Apr 2013 19:10:05 GMT

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%

Re: How can I make this summary?

rcandeo — Sat, 27 Apr 2013 01:41:07 GMT

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?

Re: How can I make this summary?

kaushiknsolanki — Sat, 27 Apr 2013 08:57:50 GMT

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