Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Group by

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.

23 Replies
Not applicable
Author

could you give us a screenshort for what you are trying to achieve

hic
Former Employee
Former Employee

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

Not applicable
Author

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)

Capture.JPG.jpg

Not applicable
Author

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

tresesco
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

I am using Table box.

Not applicable
Author

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

Not applicable
Author

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.