Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
jsakalis
Contributor III
Contributor III

Pivot RangeSum() -> Line Chart Equivalent

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!

15 Replies
swuehl
MVP
MVP

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.

sunny_talwar

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

Capture.PNG

jsakalis
Contributor III
Contributor III
Author

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

swuehl
MVP
MVP

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.

sunny_talwar

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

jsakalis
Contributor III
Contributor III
Author

Thanks again guys. Nice work!!!