topic Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total in QlikView

[ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlik Masters,

I'm back with another question. It seems that each day there are more and more interesting cases during my Qlikview Development.

Without further ado, I present you the case.

Background:

The users expect to see a report that looks like this one.

The rules for this report is that every CAPEX Type must have:

1. Top 5 Value - (Top 5 value and sort is based on the YTD value of selected Month, in this case is YTD May 2017)

2. Top 5 Total - (Total of presented Top 5 Value)

3. CAPEX Type Total - (Total YTD May 2017 for every CAPEX Type)

I don't know if this can be done in Qlikview or not. For now the only way that I can think of is to create each object for each CAPEXType, but it contains hard coding and won't be flexible if there are additional CAPEX Type in the future.

Attached with this discussion is the qvw and source file with dummy data.

Kindly take a look and please help me to solve this.

Your ideas are very much appreciated.

Cheers,

Adam Ginza

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

sunny_talwar — Fri, 11 Aug 2017 13:59:24 GMT

Somewhere along these lines?

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

Anonymous — Fri, 11 Aug 2017 14:12:52 GMT

Hi Sunny,

Thank you for the reply.

The solution given is still incorrect. Based on the report simulation given to me (Attached), there are supposed to be:

5 Descriptions for Mine Development

5 Descriptions for Exploration

5 Descriptions for Sustaining

2 Descriptions for Enhancement

And also the Top 5 Total for every CAPEX Type must be present.

Cheers,

Adam Ginza

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

sunny_talwar — Fri, 11 Aug 2017 14:57:32 GMT

Look if this is any closer (Looks like Enhancement only have 2)

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

Anonymous — Fri, 11 Aug 2017 15:05:41 GMT

Yup..this getting closer..

But I'm still confused how to add the Sub Total for Top 5..

Cheers,

Adam Ginza

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

sunny_talwar — Fri, 11 Aug 2017 15:07:43 GMT

The total is SubTotal for Top 5.... are you looking for overall total and SubTotal for Top 5?

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

Anonymous — Fri, 11 Aug 2017 15:10:40 GMT

Yes..I'm looking for Total of Top 5, and also the Overall Total for every CAPEX Type..

Cheers,

Adam Ginza

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

sunny_talwar — Sat, 12 Aug 2017 17:32:07 GMT

Like this?

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

Anonymous — Sun, 13 Aug 2017 06:08:02 GMT

Hi Sunny,

Yes..after I applied your solution to the original QVW, and checked the value one by one, it is correct.

Thank you very much.

And can you elaborate the functions the you were using. It would be really helpful.

Thanks again.

Cheers,

Adam Ginza

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

sunny_talwar — Sun, 13 Aug 2017 13:29:42 GMT

Hey Adam -

Which function are you not sure about? I am doing a lot of little things in the solution I provided, is there anything specific you don't understand my friend?

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

Anonymous — Sun, 13 Aug 2017 13:41:48 GMT

Hi Sunny,

The confusing part for me is when you put the DIV(INDEX(CONCAT())).

I don't really understand how those three functions work together in the solution. Can you explain a little bit?

Cheers,

Adam Ginza

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

sunny_talwar — Sun, 13 Aug 2017 14:25:06 GMT

That is a way I used to calculate the rank. For more details on this, I have created a post on community. Check this out

Continuous Ranking (no missing Rank)

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

qliksus — Tue, 15 Aug 2017 04:54:48 GMT

Another way of doing it through the chart presentation is by unchecking the always Pivot option and taking 2 dimension

Dimension : CapexType , CapexType, Description

Expression :

pick(Dimensionality() ,

(SUM({<Year = {"$(=MAX(Year))"},Month,Date = {"<=$(=DATE(MAX(Date)))"}>} total<CAPEXType> Actual)) ,

sum( aggr( SUM({<Year = {"$(=MAX(Year))"},Month,Date = {"<=$(=DATE(MAX(Date)))"}>} Actual)
*PurgeChar((rank(SUM({<Year = {"$(=MAX(Year))"},Month,Date = {"<=$(=DATE(MAX(Date)))"}>} Actual))<=5),'-') ,CAPEXType,Description)) ,

SUM({<Year = {"$(=MAX(Year))"},Month,Date = {"<=$(=DATE(MAX(Date)))"}>} Actual) *
PurgeChar((rank(SUM({<Year = {"$(=MAX(Year))"},Month,Date = {"<=$(=DATE(MAX(Date)))"}>} Actual))<=5),'-')
)

Re: [ASK] - Top 5 YTD Value including Total value for Top 5 and Overall Total

Anonymous — Wed, 16 Aug 2017 06:26:07 GMT

Hi Susant,

Thanks for your proposed solution. I'll use it if the users would like to view the chart simpler.

Cheers,

Adam Ginza