Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Contributor
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
Highlighted

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
Highlighted

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

Highlighted
Contributor
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
Luminary

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

Highlighted

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

www.QlikShare.com

Highlighted
Specialist
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

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

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.

QV.png

Highlighted
Not applicable

is possible to do this on load script something similar

Highlighted
Contributor
Contributor

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

Can you help?
I am doing this in Script.