last available value whith condition

[#1]

Hello All,

I have the following table:

Year Month	FLAG1	VALUE1
Mar 2012	0	48205
Apr 2012	0	48205
May 2012	0	48205
Jun 2012	0	48205
July 2012	0	48205
Aug 2012	0	48205
Sep 2012	0	49192
Oct 2012	0	49192
Nov 2012	0	49192
Dec 2012	0	49192
Jan 2013	0	49192
Feb 2013	0	49192
Mar 2013	0	49192
Apr 2013	0	49192
May 2013	1

I need, in a text box, the last available value of VALUE1 before or when FLAG=1.

in this case I should have 49192 (Apr 2013).

Regards,

Yacine

[alexandros17]

=Max({$ <FLAG1={0}>}VALUE1)

[SunilChauhan]

var=peek( 'Value1', -2 )   use in Script after above lpading table

then take =$(var) where ever you want

thanks

[Gysbert_Wassenaar]

You'll have to create a numeric value of the Year Month field. Then you can use the firstsortedvalue function to get the result you need. See attached qvw.

[#5]

this is not works good because when I dont have FLAG=1 in all the list, then I should have 0

[Anonymous]

A more appropriate response would be:

Thanks Gysbert, your reply is helpful (and I mark it so) and it works.  I have an additional request - when I don't have FLAG=1 in all the list, then I should have 0.

Regards,
Yacine

And Gysbert would tell you to modify the text box a little, for example
=if(min(FLAG1)=1, 0,
FirstSortedValue({<FLAG1={0}>}VALUE1,-[Year Month]))

[#7]

thank you for your reply

sorry I didn't explain what I want clearly.

this is not working in my case because the lists exists for all the orders. I show you in my first post only the data of one orger.

that means, some orders have FLAG=1, some other don't have it. what I need is :

- if an order dont have FLAG=1 then 0

- else, get the last VALUE1 (on last month) before or equal to FLAG1=1.

[Anonymous]

Yacine,

First, no offence meant .

Second, it is less clear now .  From you latest explanation, the result should be per order (which is not in the opening post).  On the other hand, if it is a text box, there should be one result.  I can think of  a chart where order is a dimension, so each order can have it's own result.

It would be easier to help if you could upload your application if it exists, otherwise a dataset (in Excel file, for example).  The chances are that the expression from my previous reply should work in a chart.

Regards,

Michael

PS: I have to go offline now for a while.

[#9]

it should be a text box which contain the sum of VALUE1

[Anonymous]

In this case you can use sum of the expression (I fixed it a little) aggregated by order.  If there is a field orderid unique per order, it is:

=sum(aggr(
     if(sum(FLAG1)=0, 0, FirstSortedValue({<FLAG1={0}>}VALUE1,-[Year Month]))
     ,orderid))

So, for each order, the value is 0 if there is not a single FLAG1=1, and VALUE1 for the last FLAG1=0.  That is, from the "before or equal", we use "before" - VALUE1 for last 0 per order.