# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Contributor

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

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.

Thanks,

Bino.

1 Solution

Accepted Solutions
Highlighted
Employee

Hi Bino,

`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

11 Replies
Highlighted
Employee

Hi Bino,

`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

Highlighted
Contributor

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.

Highlighted
Luminary

Hi Bino,

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

Cheers,

DV

Highlighted
Employee

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

Highlighted
Luminary

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

Highlighted
Specialist

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

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

Highlighted
Not applicable

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.

Highlighted
Not applicable

is possible to do this on load script something similar

Highlighted
Contributor

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

Can you help?
I am doing this in Script.