topic Limiting data in table since a certain date in QlikView

Limiting data in table since a certain date

Tue, 06 Dec 2011 10:58:02 GMT

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

Limiting data in table since a certain date

jagan — Tue, 06 Dec 2011 11:48:17 GMT

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.

Limiting data in table since a certain date

Tue, 06 Dec 2011 12:07:13 GMT

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

Limiting data in table since a certain date

jagan — Tue, 06 Dec 2011 12:11:14 GMT

Hi,

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

Regards,

jagan.

Limiting data in table since a certain date

Tue, 06 Dec 2011 12:16:43 GMT

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?

Limiting data in table since a certain date

jagan — Tue, 06 Dec 2011 12:26:30 GMT

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.