topic Re: Distribution Graph in QlikView

Distribution Graph

Sun, 02 Nov 2014 19:14:32 GMT

Hi All,

I have sales data that I would like to prepare a distribution graph from. So I can prepare a distribution graph for sales within certain constant bands by creating a calculated dimension.

However, is it possible to have say sales in the following bands 0-500, 5001-1000, 1001-1500 and then the last group being sales above 1500. My problem is how to create this last group i.e. no. of clients above a certain sales value as the last bar.

So far I can only create a distribution graph based on constant bands. How do I have a certain number of bands and the last band being all sales above the last band?

Hope this makes sense.

Thanks

Re: Distribution Graph

ecolomer — Sun, 02 Nov 2014 19:38:16 GMT

See an example

Re: Distribution Graph

MarcoWedel — Sun, 02 Nov 2014 19:46:47 GMT

you could use an if()/class() combination.

Re: Re: Distribution Graph

MarcoWedel — Sun, 02 Nov 2014 23:23:52 GMT

for example like this:

=Aggr(If(Sum(sales)>vSalesBandMax,Dual(vSalesBandMax&'+',vSalesBandMax),Dual(Replace(Class(Sum(sales),500),'<= x <','-'),Class(Sum(sales),500))),client)

given this data:

tabSales:
LOAD Date(YearStart(Today())+Floor(Rand()*DayNumberOfYear(Today()))) as date,
     Chr(65+Floor(Rand()*2))&Chr(65+Floor(Rand()*26)) as client,
     Money(Ceil(Rand()*1000)) as sales
AutoGenerate 100;

hope this helps

regards

Marco

Re: Distribution Graph

Mon, 03 Nov 2014 08:34:03 GMT

Hi Marco,

This is brilliant, exactly what I was looking for. Just one last quick question.

I have another field in my data which which tells me the type of business i.e. "New Business" or "Existing". How would I adjust the calculated condition if I wanted to look at purely the "New Business?

Many Thanks

Re: Distribution Graph

MarcoWedel — Mon, 03 Nov 2014 09:13:02 GMT

You could add a set expression like {$<fieldname={'New Business'}>}

in the sum function.

Please mark correct and/or helpful answers if your initial question is answered.

Hope this helps

Regards

Marco

Re: Distribution Graph

Mon, 03 Nov 2014 13:54:12 GMT

Thank you very very much, really appreciate your help.

Re: Distribution Graph

MarcoWedel — Mon, 03 Nov 2014 13:58:22 GMT

You're welcome

Regards

Marco