topic Re: Bar Chart Help in QlikView

Bar Chart Help

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

Hi guys,

I have a bar chart that I'm looking to show our top 10 suppliers on.

I should have 10 suppliers all showing as 100% (as defined by a score expression), so to then order them correctly I would do a sort on the amount of Spend.

However, it's not bringing them back in the order I would expect.

What should I specifically do to achieve this? I've tried all manner of sorts, expression limits, etc.

Many thanks,

Gareth

Re: Bar Chart Help

sunny_talwar — Mon, 21 Aug 2017 15:02:30 GMT

May be sort them using a sort expression

Sum(Spend) in descending order

Re: Bar Chart Help

Anonymous — Tue, 22 Aug 2017 08:32:23 GMT

How would I do this Sunny?

I need it to sort by score then by spend. Nothing I have tried has worked.

Thanks,

Re: Bar Chart Help

Peter_Cammaert — Tue, 22 Aug 2017 08:53:10 GMT

To make it easier to provide a working solution, please post an example document with representative data and bar chart.

Re: Bar Chart Help

omkarvamsi — Tue, 22 Aug 2017 09:24:32 GMT

Hi Gareth,

right click on chart properties-->sort tab--.>select the expression and see enable expression option on the left side and write sum(spend) in that

Regard

Re: Bar Chart Help

kusumanchir — Tue, 22 Aug 2017 09:36:37 GMT

Hi Gareth,

Please find the sample app attached and let me know if that is as expected output.

Regards,

Rasly.K

Re: Bar Chart Help

Anonymous — Tue, 22 Aug 2017 09:47:26 GMT

Hi Rasly,

Close! When I sort the table so S11 is at the top (due to having highest amount among those who scored 100), it doesn't appear unless I turn off the Dimension limit.

The chart below is what I'm trying to achieve - based on your example.

Thanks for your help.

Gareth

Re: Bar Chart Help

Anonymous — Tue, 22 Aug 2017 09:48:51 GMT

Hi Peter

Please see attached example files.

Thanks,

Gareth

Re: Bar Chart Help

Anonymous — Tue, 22 Aug 2017 09:49:24 GMT

Thanks Omkar,

Unfortunately it didn't work.

Thanks,

Gareth

Re: Bar Chart Help

omkarvamsi — Tue, 22 Aug 2017 09:55:45 GMT

@Hi Gareth,

I have modified the @kusumanchir , try this sample file.

regards

Re: Bar Chart Help

kusumanchir — Tue, 22 Aug 2017 10:27:08 GMT

Hi Gareth,

Please find the modified app with my sample data.

Regards,

Rasly.K

Re: Bar Chart Help

sunny_talwar — Tue, 22 Aug 2017 11:05:44 GMT

What is the expected output that you need here?

Re: Bar Chart Help

Anonymous — Tue, 29 Aug 2017 08:20:38 GMT

Hi Sunny,

I need a bar chart to show the top 10 suppliers based on their overall score, and where suppliers have the same score a secondary sorting using their spend is needed.

The examples I've seen work, but yet it won't work on my master data.

Re: Bar Chart Help

Anonymous — Tue, 29 Aug 2017 08:33:03 GMT

Hi all

I'm wondering if my Overall Score expression is the cause of the issue.

//Weighted Overall Score % (Comprised of Averaging the Overall PO, Invoice and Pay scores)

//Weighted as follows - PO (20%), Invoice (60%) and Pay (20%)

((RangeAvg(Count({<[Receipt Entry Date(GL)],[Receipt Entry Date(GL)]-={''} >} Distinct [PO Number])

Count(Distinct [PO Number]),

1-(Count({<[PO Number] ={"=[Invoice Date]-[Order Date]<0"}>} Distinct [PO Number])

Count(Distinct [PO Number])))*0.2)

(RangeAvg(Count({<[FTMR_P2P_KPI]={"Success"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number])

Count({<[FTMR_P2P_KPI]={"Failure","Success"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number]),

Count({<[Scanned/Tradex]={"E"} >}Distinct [Invoice Number])

Count({<[Scanned/Tradex]={"E","P","S","T"} >}Distinct [Invoice Number]))*0.6)

(RangeAvg(If(Count(Distinct [Invoice Number]) <=0, '',

If(1-(Sum([Open Amount])

Sum([$(=CTName)])) > 1, 1,

If(1-(Sum([Open Amount])

Sum([$(=CTName)])) <0, 0,

1-(Sum([Open Amount])

Sum([$(=CTName)]))))),

If(Count(Distinct [Invoice Number]) <=0, '',

1-(Count({<[Days Overdue]={'>0'}>}[$(=CName)])

Count(Distinct [Invoice Number]))))*0.2))

It doesn't help that as we work from a server, I can't save the app and attach. Also, I can't create variables hence the long expression!

Thanks,

Gareth

Re: Bar Chart Help

sunny_talwar — Tue, 29 Aug 2017 11:13:11 GMT

I am still not 100% sure I follow.... in your attached sample, which supplier do you expect to see in the bar chart?

Re: Bar Chart Help

Anonymous — Tue, 29 Aug 2017 12:19:36 GMT

So in my example,

I would expect to see SBV VItesse, FC Utrecht, PSV Eindhoven, VVV-Venlo, Feyenoord, PEC Zwolle, Heracles ALmelo, AFC Ajax, AZ Alkmaar and SBV Excelsior on the bar chart because they have they all scored 100% and then they have the 10 biggest spends among all those who scored 100%.

Re: Bar Chart Help

sunny_talwar — Tue, 29 Aug 2017 12:32:48 GMT

SBV Vltesse scored 100%? How do I see this?

10 biggest spends among these? Again where do I see this? Am I missing a sheet? I am still trying to figure out where to see this information?

Re: Bar Chart Help

Anonymous — Tue, 29 Aug 2017 13:07:31 GMT

OK,

so using the screenshot you've attached...

It should sort by Weighted Overall Score, then by Spend (if there are still

suppliers that are the same, sort alphabetically).

So, on the chart the following should be on there in this order - H, I, K,

L, M, N, B, C, D, E

Re: Bar Chart Help

sunny_talwar — Wed, 30 Aug 2017 19:41:08 GMT

Check out the attachment

Re: Bar Chart Help

Anonymous — Thu, 31 Aug 2017 08:30:03 GMT

Hi Sunny

When I look on the example you attached, it works fine.

When I try it in a real life business scenario, I get the following:

Thanks

Gareth