Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Miguel_Angel_Baeyens

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

View solution in original post

11 Replies
Miguel_Angel_Baeyens

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
Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

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
Not applicable
Author

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
Author

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
Author

is possible to do this on load script something similar

saranshbh
Contributor
Contributor

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

Can you help?
I am doing this in Script.