Set analysis cust id to cust id comparison

[#1]

Hi,

I have an application that I am using to do migration analysis over two chosen periods. I use two data islands for selecting the periods I wish to view migration over and use set analysis to come up with the values I need.

I have been asked to do a calculation that requires comparing the value of a field for one customer against that of the same customer but in another period.

So for example if I have a table with Customer ID, Rating and Assets (containing the value I want to sum). The customer then selects the periods in the data islands for period 1 and period 2 and if I plot out the results I am looking for I get something like this:

Customer ID      Rating (period 1)     Rating (period 2)     Assets (period 1)     Assets (period 2)

abc123             1.5                                1.2                             3245.21                       5231.68

def456              5.3                                6.3                             33568.596                   34712.32

ghi789              3.2                                3.0                             548                             1024.54

So then I am looking to calculate the total assets for period 2 but only for customers where the rating has decreased since period 1, and I would list these totals out by rating.

Previously when doing migration sums I would use this kind of syntax for calculating for example the sum of customers that are in both periods but where the rating has changed using a combined Customer ID & Rating field:

SUM({$<

CUSTOMER_ID =

//Get customers where rating is different between the two periods (this also picks up those that are not in both periods)

p({<CUSTOMER_ID_RATING =

           p({<PERIOD = {$(=getfieldselections(PERIOD_ISLAND1))}>} CUSTOMER_ID_RATING)

             /

           p({<PERIOD = {$(=getfieldselections(PERIOD_ISLAND2))}>} CUSTOMER_ID_RATING)>}

    CUSTOMER_ID)

*

//Intersection with first period to get customers from first period that are also in the second period

p({<PERIOD = {$(=getfieldselections(PERIOD_ISLAND2))}>} CUSTOMER_ID)

//Filter out those that occur in the second period

, PERIOD = {$(=getfieldselections(PERIOD2))}

>}  Assets)

This works for calculations accross a number of rows because you don't have to do direct field to field comparison, it is just getting a bunch of lists of field values and then filtering out those that are different in the two lists, then the ones that are from a particular period etc.

Does any one have any ideas of how I can adapt my Sets to be able to only take customers from the second period where the Rating is less than their counterparts in the first period?

I had previously had a data model that had two tables connected so that you could do direct comparisons, but it required me to have other fields in the keys eg. region, customer group etc. and because of that you would need to have all possible combinations of keys in every period otherwise items started disappearing from charts and tables. Also the amount of data is very big so a copy of the same table was very inefficient and slow to process.

[IAMDV]

Have you considered adding a helper column in the load script which evaluates, if the second period Rating is less than their counterparts in the first period?

I was about to recommend Indirect Analysis but I guess you are already using it...

Cheers - DV

[#3]

Hi DV,

  ru ther?

[#4]

Unfortunately I can't pre-evaluate during the load because the users can compare any period against any other period. They might do Q1 2011 vs Q4 2010, Nov 2011 vs Nov 2010, Nov 2011 vs May 2011. Their requirement for which periods they want to compare for their reports changes practically every month. There are some that are predictable (month vs previous month, quarter vs previous quarter, this year vs last year) but not all unfortunately.

[IAMDV]

Very interesting! That's what I thought so after recommeding the pre-evaluated method. I am sure you must have considered that path. I am sure this will be very exciting to solve. Please can you load a QV document with sample data? Not just for me, but this will be very helpful for other users as well. I would definetly wanted to try this one

Cheers - DV

[#6]

I will post up a template probably tomorrow as I will have to desensitize it.

[#7]

Ok here is a test version of it which includes the set expression I posted earlier.

Any help or ideas would be really appreciated.

[mike_garcia · Accepted solution]

It's an interesting challenge. This final expression does the trick (only added the last part, QVW Attached.):

=
Sum({$<Customer_ID = 
           p({<Customer_ID_Rating = p({<Year = {$(=getfieldselections(TO_Year))}, Month = {$(=getfieldselections(TO_Month))}>} Customer_ID_Rating) 
                                    / 
                                    p({<Year = {$(=getfieldselections(FROM_Year))}, Month = {$(=getfieldselections(FROM_Month))}>} Customer_ID_Rating)>} 
             Customer_ID) 
           *
           p({<Year = {$(=getfieldselections(FROM_Year))}, Month = {$(=getfieldselections(FROM_Month))}>} Customer_ID)
        , Year = {$(=getfieldselections(TO_Year))}, Month = {$(=getfieldselections(TO_Month))}
     > * <Customer_ID = {"=Max({<Year = {$(=getfieldselections(TO_Year))}, Month = {$(=getfieldselections(TO_Month))}>} Rating)
                            <=Max({<Year = {$(=getfieldselections(FROM_Year))}, Month = {$(=getfieldselections(FROM_Month))}>} Rating)"}
                                             >} Assets)

Mike

[IAMDV]

Mike - Awesome! Super Like

Cheers - DV

[#10]

Brilliant. That works perfectly Thanks for all the help.