Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

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.

1 Solution

Accepted Solutions
deepakk
Valued Contributor III

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

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

2 Replies
deepakk
Valued Contributor III

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

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

Not applicable

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

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,

Community Browser