Set Analysis for Max Date

[nihhalmca]

Hi All,

I need to show report account wise, sales for max (date) for instance

Account     Date             Sales

1               01-sep-13        100

1               02-sep-13        120

1               03-sep-13        130

2               05-Sep-13        150

2               06-Sep-13        160

3               08-Sep-13        140

3               09-Sep-13         110

3               10-Sep-13         100

4                11-Sep-13         200

4                12-Sep-13          290

4                13-Sep-13          280

Thanks in Advance

Nihhal

[Gysbert_Wassenaar]

Use Account and Date as dimensions and as expression FirstSortedValue(Sales, -Date). Make sure Date is a numeric date field and not string values. Use the date# function if it's necessary to convert a string to a numeric date.

[Anonymous]

Hey,

1. make sure that Date field stores a proper date (not a text), you can convert this text to date in a load script, example:

load

Account,

date(Date#(upper(Date),'DD-MMM-YY')) as Date,

Sales

inline [

Account,Date,Sales

1,01-sep-13,100

1,02-sep-13,120

1,03-sep-13,130

2,05-Sep-13,150

2,06-Sep-13,160

3,08-Sep-13,140

3,09-Sep-13,110

3,10-Sep-13,100

4,11-Sep-13,200

4,12-Sep-13,290

4,13-Sep-13,280

];

2. Create a Straight Table, add Account as Dimension and as expression add the following:

sum(aggr(if(Date=date(max(total <Account> Date)),sum(Sales)),Account,Date))

Best

K

[nihhalmca]

Thank you for response,

Not working your logic

[nihhalmca]

Thank you for response,

not working your logic

[Anonymous]

Does work. You may need to adjust the example I gave you to your scenario/data model.

[Anonymous · Accepted solution]

Example attached.

[nihhalmca]

Thank you Karol Now working fine, i hope previously i might have done mistake

[nihhalmca]

Thank You Karol

NiHhal.

[Anonymous]

Hi Gysbert,

I have checked your expression and found that only Account dimension needed to add i think its not Date and Account both with FirstSortedValue() function or i am wrong?

anant