Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

bino_joseph
New 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.

YearPortfolioSumProductValueSummary - Year - PortfolioRatio
2009A2009A10015006.67%
2009A2009A200150013.33%
2009A2009A300150020.00%
2009A2009A400150026.67%
2009A2009A500150033.33%
2009B2009B600400015.00%
2009B2009B700400017.50%
2009B2009B800400020.00%
2009B2009B900400022.50%
2009B2009B1000400025.00%
2010C2010C1100650016.92%
2010C2010C1200650018.46%
2010C2010C1300650020.00%
2010C2010C1400650021.54%
2010C2010C1500650023.08%
2010D2010D1600900017.78%
2010D2010D1700900018.89%
2010D2010D1800900020.00%
2010D2010D1900900021.11%
2010D2010D2000900022.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.

1 Solution

Accepted Solutions

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

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

9 Replies

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

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

bino_joseph
New Contributor

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

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.

Luminary
Luminary

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

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

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

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

Luminary
Luminary

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

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

srikanthsri1
Valued Contributor

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

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

Not applicable

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

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.

QV.png

Not applicable

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

is possible to do this on load script something similar

saranshbh
New Contributor

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

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

Can you help?
I am doing this in Script.