topic Re: last available value whith condition in QlikView

last available value whith condition

Fri, 07 Mar 2014 15:52:44 GMT

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

Re: last available value whith condition

alexandros17 — Fri, 07 Mar 2014 15:56:38 GMT

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

Re: last available value whith condition

SunilChauhan — Fri, 07 Mar 2014 15:57:29 GMT

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

then take =$(var) where ever you want

thanks

Re: last available value whith condition

Gysbert_Wassenaar — Fri, 07 Mar 2014 16:01:21 GMT

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.

Re: last available value whith condition

Fri, 07 Mar 2014 16:42:35 GMT

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

Re: Re: last available value whith condition

Anonymous — Fri, 07 Mar 2014 16:59:16 GMT

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]))

Re: last available value whith condition

Fri, 07 Mar 2014 17:48:52 GMT

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.

Re: last available value whith condition

Anonymous — Fri, 07 Mar 2014 18:10:55 GMT

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.

Re: last available value whith condition

Fri, 07 Mar 2014 18:23:48 GMT

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

Re: last available value whith condition

Anonymous — Fri, 07 Mar 2014 19:20:06 GMT

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.