Set Analysis for Summing two or more values for latest transaction date

[#1]

Hi Everyone,

I want to write set expression for following condition :

For one item,check latest transaction date.

Get the Unit Cost for that Latest Transaction Date,

If multiple unit cost present then sum (Unit Cost) over that same transaction & show this value against that Item in straight table.

How I can do this?

Thanks.

[deepakk · Accepted solution]

HI Kiran,

Its a tricky one. You can use the below one to get the desired result.

SUM({<Date = {$(=CHR(39)&CONCAT(aggr(mAX({<Date = {">=$(vFromDate) <=$(vToDate) "}>}Date),Item),CHR(39)&','&CHR(39))&CHR(39))}>} UnitCost)

The formula is as follows.

Chr(39) is for  ' (single quote)

vFromDate and vToDate are variables from two calendar provided to users

we are trying to get Maximum date for each itemid (in your case it might be other dimension) fro the selected two dates.

Since each item can have there own maximum dates and hence we need to use concat function to get multiple dates

Our expression will be read by qlikview as

SUM({<Date = {'01/01/2012','02/02/2012'} UnitCost)

where 01/01/2012 will be maximum date for item A

and

02/02/2012 will be maximum date for item B

I hope its clear. If not then let me know

Deepak

[#3]

Hi Deepak,

You have explained in very simplified manner.

I got the desired soultion by using Set Expression given by you.

Thanks for your correct reply,