Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggr/First Sorted Order Question

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

1 Reply
Not applicable
Author

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?