16 Replies Latest reply: Jan 19, 2016 7:55 PM by jagan mohan rao appala

# How to show data against the latest date only?

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!!

• ###### Re: How to show data against the latest date only?

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

Script solution:

Table:

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)

Resident Table;

Front end solution:

Dimension:

Customer,

DATE

Expression:

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

• ###### Re: How to show data against the latest date only?

Simply try:

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

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

• ###### Re: How to show data against the latest date only?

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

• ###### Re: How to show data against the latest date only?

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

• ###### Re: How to show data against the latest date only?

Hi,

Try like this

Dimension: Customer, DATE

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

Note : DATE should be a valid date field.

Regards,

jagan.

• ###### Re: How to show data against the latest date only?

Hi All,

Thanks for the suggestions.

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

• ###### Re: How to show data against the latest date only?

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!!

• ###### Re: How to show data against the latest date only?

Expression similar to this solved the issue for me

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

Thanks all for the help.

• ###### Re: How to show data against the latest date only?

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.....

• ###### Re: How to show data against the latest date only?

May be this:

Dimension

Customer

Expression

FirstSortedValue(DISTINCT Aggr(Sum(NPS), Customer), -Date)

• ###### Re: How to show data against the latest date only?

reddys310

Any idea on this issue.

Basically i have  customer, Score and date Columns in an spread sheet. I would need to get the score of the every customer on the latest date.

• ###### Re: How to show data against the latest date only?

Updated my last post. See if that is what you are looking for: Re: How to show data against the latest date only?

• ###### Re: How to show data against the latest date only?

Hi Sai Kumar,

Thanks and Regards,

Sangram Reddy.

###### Sunny T

Sangram Reddy

It is not suggested to use FirstSortedValue(), because it returns Null value sometimes when there is a duplicate records.

Regards,

Jagan.

• ###### Re: How to show data against the latest date only?

Hi,

Try like this

Data:

*

FROM DataSource;

LEFT JOIN(Data)

Customer,

Max(Date) AS Date,

1 AS MaxDateFlag

RESIDENT Data

GROUP BY Customer;

Now use below expression for getting the score of Customer of latest Date

Sum({<MaxDateFlag={1}>} SCORE)

Hope this helps you.

Regards,

jagan.