Limiting data in table since a certain date

[#1]

Hello,

I would like to create a table which shows a 'profit' column for a particular customer, this is the total profit he/she has gained for all time. This field is working fine.

The working expression I have for the 'all time' column is as follows:

sum({<GamingTransaction.Flag_Type_Payout={1}}>} GamingTransaction.amount)

However, I would also like to add an additional field which shows just the profit since Sept 1st 2011.

My question is, what do I need to add to the expression to limit the returned results to only those GamingTransactions that occurred after Sept 1st 2011? I have a field available 'GamingTransaction.created', which is when the transaction took place.

I have tried various things but so far none of them worked.

Any suggestions would be greatly appreciated.

Thanks,

Peter

[jagan]

Hi,

Try this

Sum({<

     GamingTransaction.Flag_Type_Payout={1},

     GamingTransaction.created=

     {'>=$(=(StartDate))'}>}GamingTransaction.amount)

StartDate is a variable which hold your from Date(Sept 1st 2011).

Regards,

Jagan.

[#3]

Jagan,

Thanks for getting back to me so quickly. The syntax of your expression works fine for me, however the result in the table is a column full of '-' entries.

I imagine this has something to do with the way I'm setting the StartDate variable. Currently, I have Date(09/01/2011).

Is this correct? Or does it depend on how I've formatted dates in the load script?

Thanks again,

Peter

[jagan]

Hi,

Can you please attach the sample file, it helps in fixing the issue.

Regards,

jagan.

[#5]

The file is about 4 gigabytes!

Or is there a better way to send an exmaple file? Copy the object in question to a new Qlikview document maybe?

[jagan]

ok fine.

Can you modify your script and add one new column as follows

Num(GamingTransaction.created) AS TransactionDateNum

Now your expression should be

Sum({<

     GamingTransaction.Flag_Type_Payout={1},

     TransactionDateNum =

     {'>=$(=Num((StartDate)))'}>}GamingTransaction.amount)

Usually I follow this method, if the date is in number format it would be easier to use it in conditions like this.

Regards,

Jagan.