topic Pivot RangeSum() -> Line Chart Equivalent in QlikView

Pivot RangeSum() -> Line Chart Equivalent

jsakalis — Thu, 09 Jul 2015 13:17:42 GMT

A colleague and I are a little stumped with creating a line chart equivalent of a pivot table (for one of the measures).

Attached is the pivot table and a line chart that approximates what we are looking for - if we can get each line to match the yellow % measure for each year, it would exactly what we are looking for.

We tried a number of different ways, unsuccessfully...

Any help with this really appreciated!

Re: Pivot RangeSum() -> Line Chart Equivalent

Fri, 10 Jul 2015 18:45:21 GMT

This is an issue I have stumbled upon in the past and could never resolve...

I'm very interested in knowing the answer!!!!!!!!!!!

Help QlikView Masters!

Re: Pivot RangeSum() -> Line Chart Equivalent

jsakalis — Mon, 13 Jul 2015 20:45:46 GMT

Any brave souls out there with the magic needed?

Thanks in advance!

Re: Pivot RangeSum() -> Line Chart Equivalent

sunny_talwar — Mon, 13 Jul 2015 20:53:00 GMT

Will work on this when I get time today. Not sure if I will have a solution, but just wanted to let you know that I would love to give it a try

Best,

Sunny

Re: Pivot RangeSum() -> Line Chart Equivalent

jsakalis — Mon, 13 Jul 2015 21:02:01 GMT

Thank you so much! In the meantime, I will scratch my head a bit more.

Re: Pivot RangeSum() -> Line Chart Equivalent

sunny_talwar — Tue, 14 Jul 2015 15:40:12 GMT

I have the straight table to show the correct numbers, but as soon as I convert it into a Line chart the sorting changes and numbers go haywire. I know why it is doing it, but don't have a solution on this yet. The problem is simple, as soon as I convert it into a line chart, it sorts the expression by the first dimension and gives the result we are not looking for. If we can find a way to sort it by the second dimension in a line chart, the chart should work for you.

I am sharing my WIP with you, as may be collaboratively we can find the solution you are looking for.

PFA the qvw

HTH

Best,

Sunny

Re: Pivot RangeSum() -> Line Chart Equivalent

jsakalis — Tue, 14 Jul 2015 15:45:07 GMT

I tried it sixteen ways myself. This should really have a simple solution - just not obvious .

Thanks for your efforts!

Re: Pivot RangeSum() -> Line Chart Equivalent

sunny_talwar — Tue, 14 Jul 2015 15:47:27 GMT

May be its time to get the experts involved here. swuehl‌, MarcoWedel‌ can you guys help?

Best,

Sunny

Re: Pivot RangeSum() -> Line Chart Equivalent

swuehl — Tue, 14 Jul 2015 16:50:46 GMT

I think you probably want something like demonstrated in the attached two line charts

(first one has a filter on MOB between 0 and 24, like in the OP line chart, note that this will not replicated the pivot table column, second with filter removed, replicating the pivot table).

Re: Pivot RangeSum() -> Line Chart Equivalent

sunny_talwar — Tue, 14 Jul 2015 16:59:14 GMT

You are such a genius Stefan. One question though, why is the filtered chart starting with MOB = 2 and not 1? And also the percentages are not matching the one up top also?

Re: Pivot RangeSum() -> Line Chart Equivalent

swuehl — Tue, 14 Jul 2015 17:23:40 GMT

Right, that's because the MOB column is a calculated dimension

=if(MOB=0,1,MOB)

while the advanced aggregation dimension MOB is not and can't be. There is a conflict because my expression can't decide if it needs to calculate for MOB 0 or MOB 1.

If this calculated dimension is needed, it would be best to calculate a MOBNEW field in the script doing the mapping of policies.

Re: Pivot RangeSum() -> Line Chart Equivalent

sunny_talwar — Tue, 14 Jul 2015 17:30:09 GMT

I just reached a similar conclusion . I just found out that Calculated dimension is throwing things off.

swuehl‌ you are legend my friend. I was able to fix the percentages to match the above pivot table and show 1 with your suggestion of creating MOBNEW

Expression:

Aggr(If(Sum ({$<MOBNEW={">=0<=24"}, @E.Year={">=2006"}>}[# de Poliza]) > 0,

1-RangeSum(Above(Sum ({$<@E.Year={">=2006"}>}[# de Poliza])/Sum(TOTAL <@E.Year> {$<@E.Year={">=2006"}>} [# de Poliza]), 0, RowNo()))), @E.Year, MOBNEW)

Output

Re: Pivot RangeSum() -> Line Chart Equivalent

jsakalis — Tue, 14 Jul 2015 17:38:10 GMT

Guys,

This is awesome!!! I will check it out later when I get a chance and mark thread answered.

Thanks so much for your dedication. Really appreciated it!

John

Re: Pivot RangeSum() -> Line Chart Equivalent

swuehl — Tue, 14 Jul 2015 17:52:09 GMT

Just take care in your production environment, that MOB field values have the correct load order, like you want to show in the line chart, numeric ascending. Advanced aggregation dimension values are always sorted by load order.

In your sample files attached, this was already the case.

Re: Pivot RangeSum() -> Line Chart Equivalent

sunny_talwar — Tue, 14 Jul 2015 17:53:28 GMT

This is what was confusing that how did it take the order we wanted. Now I understand how it did.

Re: Pivot RangeSum() -> Line Chart Equivalent

jsakalis — Tue, 14 Jul 2015 21:18:39 GMT

Thanks again guys. Nice work!!!