topic Re: Get a Max number for a pivot in QlikView

Get a Max number for a pivot

Tue, 03 Dec 2013 12:19:40 GMT

Hi,

I need to find the Max number on a pivot. This seemed simple enough to me, but struggling. I want to put the max number into a textbox.

I've attached an image of the pivot.

Can some help, a user could select diffenerent specialties months and years.

Cheers

Simon

Re: Get a Max number for a pivot

hic — Tue, 03 Dec 2013 12:32:35 GMT

You cannot get a max from a pivot. But you can calculate the same number using an Aggr():

Max(Aggr(Sum(Something),Speciality,Year,Month))

HIC

Re: Get a Max number for a pivot

Tue, 03 Dec 2013 14:30:55 GMT

THanks for the quick response. However I'm more confused now.

Let go back to the begining.

I have 2 charts and I want to have the scales the same, so I'm trying to find the Max number to fix the scale of the axes.

I want to show a line graph month by month of the number of referrals each sub specilaity has based on the specilaty I select. Graph 1 will show 1 year and graph 2 will show the previous year, but I want to fix the scales on eacvh chart so they are the same.

I've taken what you have sent me and tweaked it, but it works out the max for all specialtys not just the one i've selected.

Any help would be much appreciated

Cheers

Re: Get a Max number for a pivot

hic — Tue, 03 Dec 2013 15:31:00 GMT

Then you could do a RangeMax(Max( ...1...), Max( ...2...)) where the ...1... denote an Aggr() call equivalent to the first chart, and ...2... to the second chart. Hence

RangeMax(Max( Aggr(Count({$<Year={2012}>} ID),Month ) ), Max( Aggr(Count({$<Year={2013}>} ID),Month )))

But that would be to complicate it. You want two identical charts with different years in the two charts. Then you should make a Trellis chart with Year as first dimension. See Chart Properties - Dimension - Trellis.

HIC

Re: Get a Max number for a pivot

Tue, 03 Dec 2013 17:18:24 GMT

Hi,

Thank you for your reponses, I feel i'm getting a bit closer. Like the idea of Trellis tables, not sure its exactly what I want.

I've tried the code which works great as long as no year is selected. As long as the year is selected that I'm specifying in the code, then it works.Which does net help me when trying to compare 2 years.

Any more thoughts?