Referencing data in a crosstable

Wed, 24 Oct 2012 14:44:52 GMT

I have the following data in excel:

(and I'd like to just copy and paste this from Excel, but it doesn't work - why don't they have a SIMPLE working cut-and-paste function?!?)

User T1 T1Max T1MaxTrans T2 T2Max T2MaxTrans T3 T3Max

STKB $1 $100 100 $0.75 $300 500 $0.50 $200

MCQA $2 $50 25 $1 $100 100 $.50

- etc -

Each customer has a different number of transactions each month ("Trans"). The formula for "Charge" is (in metacode)

if Trans <= T1MaxTrans then

if Trans *T1 > T1Max, Charge = T1Max else Charge = Trans * T1

else

if (Trans>T1Max AND Trans<=T2Max) then

if (Trans-T1MaxTrans) * T2 > T2Max then Charge = T1Max + T2Max else Charge = T1Max + (Trans-T1MaxTrans) * T2

else

if IsNull(T3Max), Charge = T1Max+T2Max+(Trans-T2MaxTrans)*T3

else

if (Trans-T2MaxTrans)*T3 > T3Max then Charge = T1Max + T2Max +T3Max else Charge = T1Max + T2Max + (Trans-T2MaxTrans)*T3

I tried loading my data as a crosstable, and got a new table TransBill, with three fields: User, T1, and Data, e.g.

User T1 Data

STKB T1 $1

STKB T1Max $100

STKB T1MaxTrans 100

STKB T2 $0.75

- etc -

How do I create my formula? As an output, I want a straight table that shows:

User Monthly Trans Monthly Charge

STKB 732 $516

MCQA 182 $166

- etc -

I'm not sure how to reference the correct "data" field from the TransBill table. Any ideas?

Thanks,

Kevin

topic Referencing data in a crosstable in QlikView