topic How to compare a value from the selection to another value which is not selected. in QlikView

How to compare a value from the selection to another value which is not selected.

Wed, 10 Nov 2010 16:05:25 GMT

Hi there,

I have a list box with dates.

I want to calculate a value based on the selected date and last date. E.g. If a user selects 01/11/2010, I have to calculate previous date's balance whcih is the balance on 15/10/2010.

I am not sure how to really do it.. any help would be great.

Much Appreciated.

How to compare a value from the selection to another value which is not selected.

Wed, 10 Nov 2010 16:35:36 GMT

Hi,

If your first expression is sum(SALES)

The second should be sum({<[Statement Date]={'$(=date(max({1}[Statement Date])))'}>} SALES)

Regards

Benoît

How to compare a value from the selection to another value which is not selected.

Wed, 10 Nov 2010 16:55:19 GMT

Hi Bcuisiniere,

Thanks for your reply, it does work but Im not sure I want the maximum date in the list. I want the maximum date less than selected statement date. Can you please also help me the label part. I want it to be

Previous Balance [Date]

where Date will be previous statement date.

ta.

How to compare a value from the selection to another value which is not selected.

Wed, 10 Nov 2010 17:22:39 GMT

I made a mistake between 15/12/2010 and 15/10/2010.

then try sum({<[Statement Date]={'$(=date(max({1<[Statement Date]={"<$(=date(only([Statement Date])))"}>}[Statement Date])))'}>} SALES)

and the label

='Previous Balance '& date(max({1<[Statement Date]={"<$(=date(only([Statement Date])))"}>}[Statement Date]))

How to compare a value from the selection to another value which is not selected.

Tue, 16 Nov 2010 11:38:27 GMT

Hi mate,

Sorry was on holiday for a couple of days. It still doesn't seem to work. 😞

How to compare a value from the selection to another value which is not selected.

deepakk — Tue, 16 Nov 2010 12:35:40 GMT

HI,

I am not sure if it is a Correct approach but the attach application servers the purpose.

How to compare a value from the selection to another value which is not selected.

Tue, 16 Nov 2010 12:59:31 GMT

use peek function in the script

make

Load

Date as currentdate,

peek(Date) as previousdate

From xxx.qvd;

And use priviousdate in the chart to caculate

How to compare a value from the selection to another value which is not selected.

Tue, 16 Nov 2010 15:05:12 GMT

Hi Rishi,

Thanks for your reply.

I implemented the peak function and it corrects returns the previous statement.

However, I am unable to use it in my expression.

I am using:

=Sum({$<StatementDate={$(=PreviousStatementDate)}>} Balance)

Please let me know if I am missing something.

Thanks,

How to compare a value from the selection to another value which is not selected.

johnw — Wed, 17 Nov 2010 01:21:00 GMT

I can explain in more detail if needed, but this is probably the fifth time today I've suggested an AsOf table to solve similar problems, and I'm wearing out. See attached solution. Hopefully it's what you're looking for.