topic Re: help in expression in QlikView

help in expression

ali_hijazi — Fri, 17 Jun 2016 01:42:56 GMT

Hello

I have a pivot table with Product, and Month-Year as dimensions

I'm using the following expression to show the latest price (unit price) of the product per month:

FirstSortedValue(Price,-Date)

Now suppose an Item was bought in Feb 2016 for $15.69 and then in May for $14 let's say, then I want to display for March, and April the value $15.69

Please advise

Re: help in expression

maksim_senin — Fri, 17 Jun 2016 02:22:36 GMT

Hi Ali,

Don't you have a sample? It's hard to help without seeing of data and structure since there are a lot of variants which are applicable under different circumstances.

Best regards,

Maxim

Re: help in expression

Anonymous — Fri, 17 Jun 2016 08:17:54 GMT

=Max({<Item={$(=Only(Item)),Date=}>} Price)

Regards!

Re: help in expression

ali_hijazi — Sun, 19 Jun 2016 02:58:42 GMT

here you are ignoring date so the same value will be on all months

Re: help in expression

Anonymous — Mon, 20 Jun 2016 06:29:11 GMT

then I want to display for March, and April the value $15.69

Re: help in expression

ali_hijazi — Mon, 20 Jun 2016 07:18:41 GMT

yes but on may I don't want to have 15.69

Re: help in expression

ali_hijazi — Mon, 20 Jun 2016 07:49:28 GMT

the data looks something like this for one product:

The data is something as follows:

Date Product Value

15/2/2016 P1 15.69

25/2/2016 P1 16.5

16/3/2016 P1 17.23

5/7/2016 P1 16.75

....

for the above product I want to display the following:

for Feb 16.5

for Mar 17.23

for Apr,May, June 17.23

and Jul 16.75 and so on...

Re: help in expression

jonathandienst — Mon, 20 Jun 2016 07:53:20 GMT

This may help:

Creating a Date Interval from a Single Date

Re: help in expression

ali_hijazi — Mon, 20 Jun 2016 07:59:51 GMT

oh yeah I just forgot about Interval match

thank you

Re: help in expression

Anonymous — Mon, 20 Jun 2016 08:16:43 GMT

Then,

If you don't want to modify your script, you can use Above expression.

=If(Count(DISTINCT Value)=0,Above(Sum(Value),1),Sum(Value))

Else, you can do it with Intervalmatch or calendarice your values:

Data:

LOAD *,

Date(DateInc) as Date;

LOAD * INLINE [

DateInc, Value

01/05/2016, 10

01/06/2016, 20

01/08/2016, 50

];

LEFT JOIN

LOAD

Date,

If(IsNull(Previous(Date)),'31/12/2016',

Previous(Date)-1) as PrevDate

RESIDENT Data

ORDER BY Date DESC;

Calendariced:

Load *,

Month(Date) as Month,

Year(Date) as Year,

MOnthName(Date) as MonthYear;

LOAD

Value,

Date + Iterno()-1 As Num,

Date(Date + IterNo() - 1) as Date

RESIDENT Data

While Date + IterNo() -1 <= PrevDate;

DROP TABLE Data;

EXIT Script;

And your expression:

=FirstSortedValue(Value,-Date)

Regards!!!