Max on a date is it for all the data in the file

[#1]

I'm looking to get all the Orders for yesterday and am using DayEnd Date for this.

I'm not sure how the Max will work.

sum({<[Dayend Date] = {">=$(=Max([Dayend Date]))"} ,[Order Type] = {'O'},[Line Item Type] = {'I'}  >}[Total Line Amount])

This code works but I have some doubts about how it is working.

Say I have 3 customers who have Orders for yesterday(Dayend Date) and 2 customers that have a Order where the DayEndDate that was several days ago.

Is this code going to pick the Max date for the Customer OR the Max date for all the dates in the data?

I would want the information for the 3 customers but not for the other 2 customers.

[Nicole-Smith]

The way you have it written now, it should pick the Max over all [Dayend Date].  So, if yesterday is the Max date of all your [Dayend Date]s, it will only take data for yesterday.

[#3]

I have a similar issue, which I've tried to simplify here. I'm pretty sure it's just a question of syntax, so perhaps one of our more experienced friends can help.

In this simple example, I have two Excel data files from 'DataCo' - Company and ExpiryDate, and Company and Login Date. Each company has two expiry dates and a number of login dates. Because of poor practice at DataCo, login/passwords don't always expire on the Expiry Date. Dataco wants to know how many logins occur after the latest (max) expiry date for each of their client companies.  

Easy, right? Just use =count(if(LoginDate>ExpiryDate,LoginDate))

Except, as the attached QV file shows, that double counts some login dates. AXE only has 9 total logins, but this expression yields 10 results.The problem is each login is compared to each expiry date for a company.

OK, so let's try =count(if(LoginDate>max(ExpiryDate),LoginDate))

But QV doesn't like that expression because you're nesting aggregations. But I read somewhere that was OK if you used a qualifier like Total. So let's try

=count(if(LoginDate>max(total ExpiryDate),LoginDate))

But just returns zeros. Then I read if you put a dimension qualifier inside the max() function, it should work. So I tried

=count(if(LoginDate>max(total <Company> ExpiryDate),LoginDate)) but QV didn't like that all. Finally, I read that you need to use the "aggr()" function to

make this work, so I tried:

=count(aggr(if(LoginDate>max(total <Company> ExpiryDate),LoginDate),LoginDate))

And that doesn't work either. So, as I said at the beginning, I'm sure I'm not using correct syntax, and I would appreciate any help. Sample files attached, but this was created with my personal edition, so I hope you can open it. If not, you can create the app in seconds using the two XL files attached, and then just enter the formulas I have above. I hope you can help, because this problem - finding activity after a max date that changes for everyone recurs often at DataCo!

[#4]

I truly hope somebody can answer this for him.

I’m a Programmer but just started on Qlikview, so new I really don’t understand the syntax yet.

Lynda

Lynda Wales

800 845-3711 ext. 2366

lyndawales@unitedsportingco.com<mailto:lyndawales@unitedsportingco.com>

[Nicole-Smith]

@ frantdrakman

In your load script:

Temp:
LOAD Company as tempCompany, 
     ExpiryDate as tempExpiryDate
FROM
VC1.xlsx
(ooxml, embedded labels, table is Expires);
LoginDates:
LOAD Company, 
     LoginDate
FROM
VC2.xlsx
(ooxml, embedded labels, table is Logins);
ExpiryDates:
LOAD tempCompany as Company, 
     tempExpiryDate as ExpiryDate
RESIDENT Temp
WHERE tempCompany <> Previous(tempCompany)
Order By tempCompany DESC, tempExpiryDate DESC;
drop table Temp;

This only loads the max ExpiryDates into the ExpiryDates table.

Then, on your chart:

Dimension: Company

Expression: count(if(LoginDate > ExpiryDate, LoginDate))