Aggr/First Sorted Order Question

Fri, 14 Jun 2013 19:32:46 GMT

Hi all,

I'm dealing with cell phone data and I'd like to show:

1.) of the mobile plans that have changed in the last X months, display the previous and current plan, and

2.) of the mobile plans that have changed in the last X months, display previous cost and current cost

The field names I use/will be needed are:

PhoneLineNumber UserName MonthlyCost PlanName PlanDate

I've been trying to mess around with calculated dimensions (to show the "of the mobile plans that have changed in the last X months" portion), and I have come up with this (still doesn't account for time, only plans that have EVER changed):

=if(aggr(Count(DISTINCT PlanName), PhoneLineNumber) > 1, PhoneLineNumber, Null())

In addition to the calculated dimension, I need to show what the actual previous/current plan name and cost are. I think this will require a FirstSortedOrder() function to tell the abstract that I want the most recent plan or cost, and the most recent before that. Something like:

=FirstSortedValue(PlanDate, PlanName)

Right now, my calculated dimension isn't working, so my expression is also broken. Is this the way I should be going about the problem?

Thanks

Re: Aggr/First Sorted Order Question

Fri, 14 Jun 2013 19:37:45 GMT

I also thought about adding a field at load time which holds a bool value indicating whether or not each individual number has multiple plans assigned to it. Is that possible?

topic Re: Aggr/First Sorted Order Question in QlikView

Aggr/First Sorted Order Question

Re: Aggr/First Sorted Order Question