topic Re: Set Analysis for Summing two or more values for latest transaction date in QlikView

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

Thu, 27 Sep 2012 10:03:00 GMT

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 — Thu, 27 Sep 2012 12:17:15 GMT

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

Wed, 03 Oct 2012 09:31:40 GMT

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,