Card holders analysis

[canmarroig]

I'm attempting to create a set of dashboards for card analysis. I've a list of cards with they profile (silver, gold...). My card holders can upgrade or downgrade their profile making certain actions, on db i've the date when it happened, i need to find in a certain period (two variables in a input box) how many silver cards became gold and how many gold cards became silver. Here attached an example. Can you help me, pls? Tks

[Gysbert_Wassenaar · Accepted solution]

If you create two extra fields in the script you can easily calculate this. See attached example.

Temp:

LOAD Code,

     Profile,

     Month,

     Year,

     Coins

FROM

profile.xls

(biff, embedded labels);

Result:

load *,

    if(previous(Code)=Code and previous(Profile) <> Profile,

        if(previous(Profile)  = 'Gold' and Profile = 'Silver', 'Downgrade', 'Upgrade')) as Change,

    date(MakeDate(Year, Month),'M-YYYY') as Date

Resident Temp

order by Code, Year, Month;

drop table Temp;

[canmarroig]

Thank you, I was studing a way to solve in front end but script is easier.