topic Problem with Calculated Dimension and Rank in Line Chart in QlikView

Problem with Calculated Dimension and Rank in Line Chart

Fri, 20 Nov 2020 18:02:30 GMT

Hi,

I am having trouble with using Rank in a calculated Dimension when using a line chart.

My Straight Chart (QVStraightChart1) has first expression:

YTD 2014 Sum({$<Year={"$(vMaxYear)"}, MonthID>}Volumes/Sum({$<Year={"$(vMaxYear)"}, MonthID>}Total(Volumes))

2013 is {"$(vMaxYear-1)"} etc

And has been restricted to show only 15 rows on the Presentation tab - all good here!

My Line Chart (QVLineChart) has the Calculated Dimension to show top 10 items only:

=IF(Aggr(Rank(Sum({$<Year={"$(vMaxYear)"}, MonthID>}Volumes/Sum({$<Year={"$(vMaxYear)"}, MonthID>} Total(Volumes)),4), Item) <=10, Item)

And the expression:

Sum({<MonthID>}Volumes)/Sum({<MonthID>}Total <Year>(Volumes))

However the figures are all wrong (QVLineChartFigures):

If I change the Calculated Dimension to <=50, the figures are then correct. Can anyone help?

If I use an expression of Sum({<MonthID>}Volumes/Sum({1<MonthID>} Total <Year> (Volumes)) the figures are all wrong.

Can anyone help?

Re: Problem with Calculated Dimension and Rank in Line Chart

JonnyPoole — Fri, 28 Nov 2014 18:25:03 GMT

A QVW would help. I'm sensing that while the expression is syntatically valid and giving you numbers, there could be an order of operations issue with the division or perhaps some null handling coming in .

If you can't share try testing it with just the numerator or the denominator

numerator rank

=IF( Aggr(Rank(Sum({$<Year={"$(vMaxYear)"}, MonthID>}Volumes,4), Item) <=10, Item)

denominator rank

=IF( Aggr(Rank(Sum({$<Year={"$(vMaxYear)"}, MonthID>} Total(Volumes)),4), Item) <=10, Item)

to see if ranking without the division yields the right values for top and bottom.

If those numbers are good / bad that will determine the next step.

Re: Problem with Calculated Dimension and Rank in Line Chart

Mon, 01 Dec 2014 09:43:44 GMT

Hi Jonathan,

Is it possible to send you the qvw off the public domain?