topic Dynamic X-Axis in Line Chart in QlikView

Dynamic X-Axis in Line Chart

Thu, 07 Mar 2013 19:12:07 GMT

Let me first say this has been a challenge for me, and my developers, for years. We almost threw in the towel but decided to ask the QlikCommuity for any ideas that could possibly lead us to a solution. I've attached our .qvw sample document for reference.

The best way I can explain it is that we're trying to dynamically limit our X-axis on a line chart based on the expression, in this case a percentage. The challenge is our expression is "Full Accumulation", so using Dimension Limits or Static Max in the Axes tab isn't straight forward. All of or research pointed to the use of the fractile() function but even that is giving us weird results.

For you guru's out there looking for a challenge, if you could take a look at the .qvw we put together we would be greatly appreciatative. Thanks for anything.

Re: Dynamic X-Axis in Line Chart

kaushiknsolanki — Fri, 08 Mar 2013 08:00:14 GMT

Hi,

Please have a look at the attched example.

is this what you want?

Regards,

Kaushik Solanki

Re: Dynamic X-Axis in Line Chart

Anonymous — Fri, 08 Mar 2013 14:47:54 GMT

I WAS able to create an
expression that will do the accumulation without having “Full Accumulation”
checked and then have it calc for only the current qtr:

RangeSum(above(sum({<[CY Qtr Current
Flag]={1}>}Units)/sum({<[CY Qtr Current
Flag]={1}>}Total Units),0,RowNo()))

If you change to a straight
table and sort by quarter, you will see that it works the same as your original
accumulated expression. My thought was that I could then use this as the
first (invisible) expression along with Dimension Limits to stop showing values
when the expression reaches 95% for example.

I also tried some iteration of
using Fractile around an Aggr statement, but didn’t get anywhere.

I am not having luck closing the
deal and have run out of hours to spend on this.

That’s a tough one.

Re: Dynamic X-Axis in Line Chart

Fri, 08 Mar 2013 16:17:07 GMT

Kaushik - First thank you for your time looking into this challenge. Great idea but unfortunetely not the solution. If we wanted to only show 95% of the average on the X axis this would be the solution but we need 95% fractile on the X axis. I constantly struggle with average vs fractile but this site explains it well:

http://firechief.com/mag/firefighting_performance_standards_mean

So using a 95% average for market "LEZ" you get 494, what we are trying to get is the fractile for that which I would estimate to be around 300 (looking at the other charts). Your solution would be perfect if somehow we could replace average with fractile ... would you know how to do this? Thanks again!

Re: Dynamic X-Axis in Line Chart

Gysbert_Wassenaar — Fri, 08 Mar 2013 17:09:32 GMT

See attached qvw. Maybe that's closer to what you're looking for. It's based on this post. Mind, perfomance may not be all that wonderful. YMMV.

Re: Dynamic X-Axis in Line Chart

Fri, 08 Mar 2013 18:18:58 GMT

Gysbert thank you. Interesting creating a new distinct PriceN field in the load and then using that in your aggr() expression. Unfortunetely this isn't what I was looking for either. Check out Market "Fkpis" in you solution, X axis goes past 1000. I would want it to be the 95 percentile for the current quarter, which is somewhere around 210. It works great for some combinations and doesn't for others ... depending on the nature of the data.

I tried to use a combination of aggr() and fractile() with set analysis below to limit on current quarter and Price under 1000 but couldn't nail it. Again I really appreciated the feedback you provided.

({$<[CY Year Current Flag] = {'1'},Price ={"<=1000"}>}

Re: Dynamic X-Axis in Line Chart

Fri, 08 Mar 2013 19:37:25 GMT

Gysbert - Honestly I need some time to soak your idea in, I think it will work but I need more time to understand exactly what is going on. One thing weird I noticed is that "Gbmwd" Market does not return a value for the expression you created below. The nature of the data this selling price is very high but again why would it not even return a value like all the other ones. This is something I need to look into, also the post you referred to above. THANKS

=min(Price, $(=1 - sum(aggr(sum({<[CY Year Qtr]={'CY2012 Q3'}>} if(Price<=PriceN,Units))/sum({<[CY Year Qtr]={'CY2012 Q3'}>} total <[CY Year Qtr]> Units)<0.90,PriceN))))

Re: Dynamic X-Axis in Line Chart

Mon, 18 Mar 2013 19:28:44 GMT

It took me some time to understand what was going on but I finally got it. Thank you so much for this solution and the link you provided.