Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Can any one help me with the below.
I have country and Date dimension and a fact.
I want to select country and respective max date.
e.g.: AUS May 01- 2014
AUS May 27 2014.
I want to display country and max(date) for that country in dashboard. I have used MAX(Date). But having multiple entries for the same country.
Can any one tell, how to retain one entry per country.
Thanks in advance.
could you give us a screenshort for what you are trying to achieve
If you want just one entry per country, you should not have Date as dimension. Just create a chart with country as the only dimension and Max(Date) as expression.
HIC
I want to have distinct max date values per country.
DIM: Country , Date
FACT : country_cd,date_key, Measure
I want to display all the countries present in fact with max(date)
or if you need to use it as dimension you can use a calculated dimension and you wirte there:
aggr(max(Date),Date)
Hope this helps.
regards,
MT
This could be because of using improper way to calculate Date field at the script. Use Date() to calculate the field, like:
Load
Date(DateField) as Date
Problem I am facing is I am getting two entries per country. Want to group by country.
I want to do something like this.
Select Country, Max(Date)
Group by Country
Result : AUS - 6/27/2014
I am using Table box.
for your needs you cannot use a table box it will just show you every entry you have in the table you are looking at. in you case i would suggest you a pivot table and then you can use for Date a calculated dimension with the expression
aggr(max(Date), Date) or you use Date as an expression instead of a Dimension and you jsut wirte max(Date)
I hope this helps.
regards,
MT
you should not use Date as dimension field if u want the max(date) and only 1 row.As Date is in dimension it wil give you all the values.