Maximum of Multiple values over one dimension

[madushanfernand]

Hi All,

In my data source i have a table like below,

Invoice #	Actual On Board Date
16652	23/07/2018
16652	23/07/2018
16652	23/07/2018
16652	23/07/2018
16652	23/07/2018
16652	23/07/2018
16653	24/07/2018
16654	25/07/2018

What my objective is to create a table like this,

Invoice #	No Of Days
16652	32
16653	31
16654	30

Here No Of Days  is calculated by the formula

No Of Days = Today()-Actual On Board Date

i am getting NULL for the invoice number 16652 in Qlikview chart which i want to create. Other invoice numbers gets correct values. i think this is because the invoice number 16652 has multiple dates. any how Highly appreciate if some one could help me sorting this out.

Thank you

[gmoraleswit]

try this as the expression:

Today()-max(Actual On Board Date)

[madushanfernand]

Hi Gabriela

Thank you for your response.. I have tried this before..but didn't work.. this gives null for the first row of the 2ND table..

[gmoraleswit]

Can you send an example QVW?

[neelamsaroha157 · Accepted solution]

It looks like you have a timestamp field but displaying in the date format. Can you try loading the field as Date(floor(Actual On Board Date), 'YourDateFormat').

and as Gabriela suggested, you can use max with the aggregation as well, like -

Today()- Aggr(max(Actual On Board Date), [Invoice #])

[madushanfernand]

Thank a lot for both of your support. Neelam, It worked with Aggr funtion.

thanks once again.