To show sum a group of values of same criteria as an expression in qlikview

[Anonymous]

Hi,

I have been try to achieve the summary of a particular selection as shown in the excel sheet on qlikview.

I have tried aggr, set analysis and have not reached anywhere.

Appreciate if someone could help on this.

Year	Portfolio	SumProduct	Value	Summary - Year - Portfolio	Ratio
2009	A	2009A	100	1500	6.67%
2009	A	2009A	200	1500	13.33%
2009	A	2009A	300	1500	20.00%
2009	A	2009A	400	1500	26.67%
2009	A	2009A	500	1500	33.33%
2009	B	2009B	600	4000	15.00%
2009	B	2009B	700	4000	17.50%
2009	B	2009B	800	4000	20.00%
2009	B	2009B	900	4000	22.50%
2009	B	2009B	1000	4000	25.00%
2010	C	2010C	1100	6500	16.92%
2010	C	2010C	1200	6500	18.46%
2010	C	2010C	1300	6500	20.00%
2010	C	2010C	1400	6500	21.54%
2010	C	2010C	1500	6500	23.08%
2010	D	2010D	1600	9000	17.78%
2010	D	2010D	1700	9000	18.89%
2010	D	2010D	1800	9000	20.00%
2010	D	2010D	1900	9000	21.11%
2010	D	2010D	2000	9000	22.22%

I have year and portfolio and the corresponding value as shown above.

In the column, Year-Portfolio summary, I need to show the total sum for the respective year and portfolio so that I can check the percentage of this value for the whole of the portfolio for that year.

Eg. for 2009 and product A, the sum of values = 100+200+300+400+500 = 1500 - which is shown in Summary - Portfolio - Year

This has to happen for all respective year and portfolio.

I know it should be something achievable but somehow I am not able to figure out how to do it.

I could do it on excel -- see attachment, but not on qlikview.

Please help.

Thanks,

Bino.

[Miguel_Angel_Baeyens · Accepted solution]

Hi Bino,

The TOTAL will help you here:

Sum(TOTAL <Year, Portfolio> Value)

Will return the sum for each Year/Portfolio, even if you don't have the calculated field. So the percentage will work as

Sum(Value) / Sum(TOTAL <Year, Portfolio> Value)

Check the application attached just in case.

Hope that helps.

Miguel

[Anonymous]

Hi Miguel,

That looked so simple....I was trying all complex formulas.....and complicating it....

This will surely help me a lot.

Thanks a lot..!!!!....Appreciate it...!!!!

Regards,

Bino.

[IAMDV]

Hi Bino,

Please can you mark Miguel's post as answered? This helps other users to navigate to the answer.

Thanks in advance.

Cheers,

DV

[Miguel_Angel_Baeyens]

Thanks DV,

It's actually more useful when the answers are properly marked to search for and see what has helped even if your issue is not exactly the same discussed in the threads.

Anyway, I'd set your blog post on how to get the best from QlikCommunity as the landing page from time to time, you know, just in case...

Appreciated,

Miguel

[IAMDV]

Thanks Bino

Thank you Miguel. I personally learn a lot from your posts and it's important to mark the posts as "answered". I'm sure it helps everyone of us.

That's nice to know that you use my post. I'm writing one more post on similar lines and I'll keep you posted.

Cheers,

DV

www.QlikShare.com

[Anonymous]

Hi gurus,

              i too have the same problem ..i tried using total but problem is i need to conditionally pick only +ve values form my data..

i need an expression like

total(if(sum(sales)>0,sum(sales)..but that expression is not acceptable ..can you please help me with this problem

this is my post ..i need an exprssion for relative ..i want my chart to be in pivot not straight table

http://community.qlik.com/message/215560#215560

[#8]

I am in a exact same situation but SUM by Group is somehow not working for me. Here is the screenshot. Can somebody tell me what's wrong here. I am trying to sum the interval grouping them by Ticket ID.

I have tried following so far but none work here.

Sum(TOTAL <[Ticket ID]> Interval)

sum(aggr(DISTINCT interval, [Ticket ID]))

Thank you.

[#9]

is possible to do this on load script something similar

[saranshbh]

I have 5 columns. 
Unable to do this with a similar code.

Can you help?
I am doing this in Script.