topic Re: Displaying values - Combo chart on DataPoints in QlikView

Displaying values - Combo chart on DataPoints

Wed, 26 Oct 2016 06:42:47 GMT

Hello,

I want to display the Total values on top of each barwhich i have acheieved by using using an expression and making it invisible. However when i introduce a line graph, the values for the Bars are being shown for the line graph's data points which is wrong. Please help to resolve this issue.

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Wed, 26 Oct 2016 10:17:25 GMT

Try moving this expression for label above the expression for Sales and see if that helps. Also, have you made sure that you have no enabled the 'Values on Data Points' for the Sales expression?

Re: Displaying values - Combo chart on DataPoints

Wed, 26 Oct 2016 10:32:49 GMT

Yes i tried all these possibilities, but no luck... Is there any work around?

Thanks.

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Wed, 26 Oct 2016 10:34:46 GMT

Would you be able to share a sample where you can replicate the issue for us to play around with?

Re: Displaying values - Combo chart on DataPoints

Wed, 26 Oct 2016 15:04:18 GMT

I have attached a sample application here. Please refer to the main tab.

Also i have a second question on the By Country tab in the application. My requirement is to display the bars for countries and the stacks should be the sellers with their % contribution, please give your suggestios on that too. Thanks.

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Wed, 26 Oct 2016 15:05:53 GMT

I don't see any attachment, can you reattach please?

Re: Displaying values - Combo chart on DataPoints

Wed, 26 Oct 2016 15:17:11 GMT

Yeah missed it, attached now. Please take a look.

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Wed, 26 Oct 2016 15:22:01 GMT

This seem to have worked

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Wed, 26 Oct 2016 15:29:49 GMT

Something like this for your second issue?

Expression:

FirstSortedValue(Aggr(Sum(TotalBalance),Date,BalCtry,Seller),-Aggr(Fisc_day_of_month,Date,BalCtry,Seller))/Sum(TOTAL <BalCtry> Aggr(FirstSortedValue(Aggr(Sum(TotalBalance),Date,BalCtry,Seller),-Aggr(Fisc_day_of_month,Date,BalCtry,Seller)), BalCtry, Seller))

Re: Displaying values - Combo chart on DataPoints

Wed, 26 Oct 2016 15:58:57 GMT

Perfect , Thanks Sunny.

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Wed, 26 Oct 2016 16:00:41 GMT

Awesome, I am glad it all worked out.

I would request you to close this thread by marking correct and helpful response as this will be helpful for other visitors in the future.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Displaying values - Combo chart on DataPoints

Wed, 26 Oct 2016 16:00:57 GMT

This seems to work if i dont apply any Dimension limits, but when i restricted the Seller to show only the Top 5 , the Total percentage shrinks.

I would want the Percentage for Australia to be at Same, and the bar should show the Top 5 Sellers with percentage and the rest % should be under "Others". Why does not it work if i just apply the Dimensions limit?

Thanks.

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Wed, 26 Oct 2016 16:02:32 GMT

Would you be able to put down the numbers you want to see for each of the two countries in a table form so that I can see what you actual want?

Re: Displaying values - Combo chart on DataPoints

Wed, 26 Oct 2016 16:28:20 GMT

I just noticed that the FirstSortedValue function does not pick the expected value, i want the most recent value according to the dates to be picked for every seller. For example, for trhe Seller 525(CC), the most recent value is 2410 which is on 6th Oct 2016 and that should be picked.

Date	0-30	31-60	61+	TotalBalance	BalCtry	SellerSK
21-Jan-16	1827.75	2558.85	2924.4	7311	Aus	525
11-Feb-16	1557.5	2180.5	2492	6230	India	525
24-Mar-16	880	1232	1408	3520	Aus	525
14-Apr-16	851.5	1192.1	1362.4	3406	India	525
2-Jun-16	890	1246	1424	3560	India	525
30-Jun-16	851.5	1192.1	1362.4	3406	Aus	525
8-Sep-16	583.25	816.55	933.2	2333	India	525
6-Oct-16	602.5	843.5	964	2410	Aus	525

And this value should be used for calculating the percentage.

(Most recent Balance of Seller)/(Most Recent Balance of Entire Country).

Most recent balance of the Country will be the sum of balances across Sellers at their most recent dates, which will vary for each seller.

Please let me know if you need more information.

Thanks.

Re: Displaying values - Combo chart on DataPoints

Wed, 26 Oct 2016 16:48:26 GMT

Attaching the qvw file here.

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Wed, 26 Oct 2016 23:48:14 GMT

Solving the first part of the issue to pull the most recent value. Try this:

FirstSortedValue(Aggr(Sum(TotalBalance),Date,BalCtry,Seller),-Aggr(Date,Date,BalCtry,Seller))

Working on the % part now

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Thu, 27 Oct 2016 01:47:40 GMT

Is this the goal?

Re: Displaying values - Combo chart on DataPoints

Thu, 27 Oct 2016 05:06:04 GMT

Thanks for the solution,Howeveri will not be able to group it on Dates only as I have to pick the recent value based on the Fiscal Months, that is the reason for using Fiscal_day_of_month in the Aggr.

FirstSortedValue(Aggr(Sum(TotalBalance),Date,BalCtry,Seller),-Aggr(Fisc_day_of_month,Date,BalCtry,Seller))/Sum(TOTAL <BalCtry> Aggr(FirstSortedValue(Aggr(Sum(TotalBalance),Date,BalCtry,Seller),-Aggr(Fisc_day_of_month,Date,BalCtry,Seller)), BalCtry, Seller))

Re: Displaying values - Combo chart on DataPoints

sunny_talwar — Thu, 27 Oct 2016 08:41:34 GMT

The issue is that Fisc_day_of_month is smallest here:

So FirstSortedValue() function will pick the second last row based on Fisc_day_of_month. You need to either use Fisc_Period or Date to meet your requirement. Using Fisc_day_of_month, you will never meet you requirement.

Best,

Sunny

Re: Displaying values - Combo chart on DataPoints

Wed, 02 Nov 2016 12:28:54 GMT

Is it possible to display the percentages on every Segment?

And can we fix the "Others" with a persistent color part to be at the top at all times?

And this chart should not react to any of the Selections in the Seller dimension.

Thanks.