Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
=Max({$ <FLAG1={0}>}VALUE1)
var=peek( 'Value1', -2 ) use in Script after above lpading table
then take =$(var) where ever you want
thanks
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.
this is not works good because when I dont have FLAG=1 in all the list, then I should have 0
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]))
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.
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.
it should be a text box which contain the sum of VALUE1
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.