How to get latest transaction date?

[Loed]

Hi guys,

Just wanted to ask how to get the LATEST transaction date for every customer using INFORMATION DESIGN TOOL?

For example, this is my raw data:

Transaction Date	Customer	Material	Amount
20160105	A	X	10
20160121	A	X	20
20160210	A	X	30
20160225	A	X	40
20160304	A	X	50
20160316	A	X	60
20160103	B	X	70
20160108	B	X	80
20160124	B	X	90
20160206	B	X	100
20160215	B	X	110
20160218	B	X	120

I wanted to have this query:

My parameter is transaction date..So if user entered 20160201 - 20160229, this will be my result..

Customer	Last Transaction Date	Amount
A	20160225	40
B	20160218	120

So if user again entered 20160101 - 20160131, this will be my result..

Customer	Last Transaction Date	Amount
A	20160121	20
B	20160124	90

I tried to use the MAX function but did not get the desired result..Is there a CONDITION in universe similar to query designer?

Thank you.

Loed

[Former Member · Accepted solution]

Hi,

You need to create a derived table in the data foundation like this:

SELECT

  A.Customer,

  Max(A.Last_Transaction_Date) as Last_Transaction_Date,

  Sum(Amount) as Amount

FROM My_Table B,

  (SELECT Customer, Last_Transaction_Date

  FROM My_Table

  WHERE

  Last_Transaction_Date  BETWEEN  @Prompt('Start Date','D')  AND  @Prompt('End Date','D')

  GROUP BY

  Customer,

  Last_Transaction_Date) A

  Where B.Customer = A.Customer And B.Last_Transaction_Date = A.Last_Transaction_Date

  GROUP BY  A.Customer

Then in the Business, Layer you create objects Customer,, Last Transaction Date and Amount based on the derived table.

And you will have only one row for a customer corresponding to the Max Last_Transaction_Date including on the dates range selected in the 2 @Prompt.

Didier

[Loed]

Hi,

Thanks a lot guys for the help especially to and ..

I also found another approach similar to yours by using the ROW_NUMBER() OVER (PARTITION BY <fields> ORDER BY <fields>) command..

Thanks for the trick..

It did also solve my scenario but the only problem I encountered is the PERFORMANCE..I am playing with several data here so I think that's the only downside but it's the easiest way for smaller size data..

Regards,

Loed

[former_member207052]

Hi - Since the calculations happen at the database end it should be faster and thats the reason I suggested you to delegate as I assumed the size of the data when "Transaction" date is used (or At least in SAP world). Also, since the load is on the database when you run a big report, other reports/users will not be affected. (Please check the link below and other official docs on database delegation)

What's more important is which method works for you and glad you have found the right one. Cheers!

Varun: Check the below blog which talks about delegation.

Using Database Delegated Measures or Smart Measures in Web Intelligence - Business Intelligence (Bus...

[Loed]

Hi,

Really? I don't know why it's not happening on my end..

Anyway, I will try again to play with DELEGATE option..

Thanks again for the trick..

By the way, may I ask why you used MEASURE instead of DIMENSION for getting the MAX(DATE)? Will the result differ if I use DIMENSION?

Regards,

Loed

[former_member207052]

No- the results will not change as MAX can be even applied to a string. I created measure because

1. It can be delegated
2. Even though you call it as a "date" I considered is as a bigint in my table design, which really is  AND I was lazy to format the number as date in table(just copy, pasted values)

[Loed]

Hi,

Thanks a lot for the explanation..

Regards,

Loed

[former_member207052]

Delegate!

Create two delegated measures with MAX as the aggregation function. No other SQL/Derived Table required.

Try Following the screenshots.

[Former Member]

HI Narashiman,

Very nice trick . Can you explain what will happen if we keep database delegate ?

what will happen actually backend?

Any help highly appreciated

Thanks,

Vaurn

[Former Member]

HI Loed,

May  I know Why you want in IDT

You can achieve in Webi report level .by keeping Transcation date in query filter and applying prompt and after that we can apply user response

Correct me if i am wrong.

Thanks,

Varun

[Loed]

Hi,

I think you have similar idea with Yuvraj above? But that is not the problem..I need to get the latest transaction date of each customer based on the parameter used by the user..

Regards,

Loed

[Former Member]

Hi Loed,

Try this,

1. Edit the query and apply a prompt on <Transaction date> object.

2. Choose the operator as "Between". This will give two parameter fields: start and end.

3. As it is a date prompt, user should get calendar option to choose dates as far as i am sure.

4. User can choose start date as 20160201 & end date as 20160229 and run the query.

Regards,

Yuvraj

[Loed]

Hi,

I'm not sure if you understood my problem? I need to get the latest transaction date..Also, yes I do have a prompt with dates so that user can enter their desired range..

Regards,

Loed

[Former Member]

Hi Loed,

Have you seen my answer?

I tested and it exactly fits your requirement.

Didier

[Loed]

Hi Didier,

Yes, currently testing it..I'm still figuring out the combination of the fields I need so I will achieve the right value of amount..

I will keep you posted..

Thank you..

Loed

[Former Member]

Hi Leod,

Can you try creating the max(Transaction Date) object and use it in the report level.

Unlike sql query the webi will form the query like below. So just give a simple try

SELECT

  max(Outlet_Lookup.Date_open),

  sum(Shop_facts.Amount_sold),

  Outlet_Lookup.State

FROM

  Outlet_Lookup,

  Shop_facts

WHERE

  ( Outlet_Lookup.Shop_id=Shop_facts.Shop_id  )

  AND 

  Outlet_Lookup.Date_open  BETWEEN  @Prompt('Enter value for Opening date (Start):','A','Store\Store details\Opening date',Mono,Free,Persistent,,User:0)  AND  @Prompt('Enter value for Opening date (End):','A','Store\Store details\Opening date',Mono,Free,Persistent,,User:1)

GROUP BY

Outlet_Lookup.State