How to show data against the latest date only?

[#1]

Hi All,

How can i show only the data against the latest date from an spread sheet. The data would be as follows:

Customer               DATE                     SCORE

       A                  4/15/2015                      60

       B                  4/15/2015                      50

       A                  6/15/2015                      55

       D                  6/15/2015                      40

I would only need to show the records against 6/15/2015

TIA!!

[sunny_talwar]

In the script or on the front end of the application?

Script solution:

Table:

LOAD * Inline [

Customer,               DATE,                     SCORE

       A,                  4/15/2015,                     60

       B,                  4/15/2015,                      50

       A,                  6/15/2015,                      55

       D,                  6/15/2015,                      40

];

Right Join (Table)

LOAD Max(DATE) as DATE

Resident Table;

Front end solution:

Dimension:

Customer,

DATE

Expression:

=Only({<DATE = {"$(=Date(Max(DATE)))"}>} SCORE)

[Anonymous]

Simply try:

=sum({<Date=, Date={'=$(=date(max(Date),'DateFormat'))'} >} Score)

Make sure Format should be same for both Date and Expression of max date

[markodonovan]

Hi there,

You could also add the set analysis to a variable such as max_date:

='DATE={"'& date(max(date#(DATE))) &'"}'

and use it in an expression:

sum({< $(max_date) >} SCORE)

See the attached document example:

Thanks

Mark

http://techstuffybooks.com

[sudhir0538]

Hai Sai

Declare a variable like vYesterday==date(Today()-1,'dd-MM-yyyy'), and mentioned it in your query like this.

Sum({<DATE={'$(vYesterday)'}>}Score)

It's a kind of set analysis.

Sure it'll work

[jagan]

Hi,

Try like this

Dimension: Customer, DATE

Expression: Sum({<DATE = {"$(=Date(Max(DATE)))"}>} SCORE)

Note : DATE should be a valid date field.

Regards,

jagan.

[#7]

Hi All,

Thanks for the suggestions.

Some how im not successful in solving the issue. Attaching my app and sample data. !

[#8]

In the app, i would need to display AVG of NPS for all the customers on 6/1/2016 (i.e. Latest Date) on the guage above and AVG per customer on the Bar chart below.

In very urgent Help.

TIA!!

[#9]

Expression similar to this solved the issue for me

"sum({<Date={'$(=Date(max(Date)))'}>} NPS)"

Thanks all for the help.

[#10]

Guys need another help on this.

I would actually need the Score against each Customer on the latest date.

Ex in the sample data provided, i would need it this way :

A      60

F      85

Various  136

M     50

N      50.....