Simple question

[#1]

In the attached file, I have a field named "Company" and a field named "LoginDate". I want to find the latest date a company logged in, and count all the logins one year (i.e. 365 days) back from that date. E.g. looking at the Table Box, for company "ABC", the last login date is 5/16/2009, and from 5/16/2008 to 5/16/2009 there are four logins.

I have tried so many different forms of Set Analysis, my mind is swimming with brackets and parantheses. No avail.

Sample file attached.

Can anyone help?

[lironbaram]

hi attach is an example

set analysis won't work in your case

because set analysis is like making a selection in the model

it will apply one date to all companies

hope this helps you

[#3]

Thanks, that worked, but wow - in my real table, with literally millions of records, that takes forever to recompute!

Also, in the formula, you wrote:

=sum(aggr(if(LoginDate>=AddMonths(max(total <Company> LoginDate),-12),1,0),LoginDate,Company))

I'm just curious .. why do you add the <Company> in the "max(total LoginDate)"? Does this constrain the "max" function to compute a different max logindate for each company? Is this a generally available construction? E.g. if I had a bunch of "Regions" like Europe, America, etc.,

would sum(total <Region> Sales) generate the sum of sales for each region?

[nstefaniuk]

Hello.

In my opinion you can't do that easily, especially on huge number of lines, because you can't use a trick like liron baram has given.

You should, in script, calculate the last date and flag all the records, on each company, that are after the last date of this company - 365.

And so it would be very easy to count the records using flag = {"1"}.

[nstefaniuk]

Other solution, if you have a known number of company is to do:

=count({$<Company={"A"}, EventDate={">=$(=Date#(max({$<Company={'A'}>}EventDate)-365))"}>} EventDate)

+ count({$<Company={"B"}, EventDate={">=$(=Date#(max({$<Company={'B'}>}EventDate)-365))"}>} EventDate)

+ count({$<Company={"C"}, EventDate={">=$(=Date#(max({$<Company={'C'}>}EventDate)-365))"}>} EventDate)

[#6]

Thank you for your suggestions; unfortunately, with 3500+ customers, solution B doesn't sound like it would work.

I am interested in your solution A. How, exactly, would you compute "MaxLoginDate" for each "Company" in the script?

[swuehl]

Maybe like this (added to the script):

LASTLOGIN:

LOAD *,

if(AddYears(LastLoginDate,-1)<=LoginDate,1,0) as InLastYearFlag;

LOAD *,

if(peek(Company)<>Company, LoginDate, peek(LastLoginDate)) as LastLoginDate

Resident Sheet1 order by Company, LoginDate desc;

drop table Sheet1;

Then use the created InLastYearFlag to sum up the Logins that happened within a year back from the last login.

Hope this helps,

Stefan

[#8]

Thanks, Stefan, but perhaps I wasn't clear - there is NO "LastLoginDate" field in my data - there is only Company, Status - which is expired or not - and LoginDate. so the task was to find the last login date for a company, and if its status is "expired" (that part is simple!), count the number of logins for the last year. Each login is a timestamp in format "mm/dd/yyyy hh:mm:ss". In a table, max(LoginDate) works fine, but when I tried to do counts, QV chose the last login date of anybody as the last login date for "everybody", which is clearly wrong.

But if you could answer this question, I would be grateful! In the construction:

"max( total <Company> LoginDate)", does the term <Company> constrain the max function to return the last LoginDate for that specific instance of Company? In other words, in a table with Company as the first (and only dimension), if the first company is "ABC", then it returns the last login for ABC, if the next line of the table is "DEF", then it returns the last login for "DEF", etc.? it seems to be doing this, but I'd like to know if I can extend this to a general principle for all the aggregation functions without a lot of trial and error testing.

[swuehl]

Yes, you were clear, that's why I created the LastLoginDate field in my load script...

Adding my above script to the load will generate the LastLoginDate and the flag field, so something like this in a table with dimension Company should give you what I think you want:

=if(FirstSortedValue(Status,-LoginDate) = 'Expired',sum(InLastYearFlag) )

I think you are right with the total qualifier, this is what the HELP says:

If the word total occurs before an expression, the calculation will be made over all possible values given the current selections, but disregarding the chart dimensions.

The total qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimensions. In this case the calculation will be made disregarding all chart dimensions except those listed, i.e. one value will be returned for each combination of field values in the listed dimension fields. Also fields which are not currently a dimension in a chart may be included in the list. This may be useful in the case of group dimensions, where the dimension fields are not fixed. Listing all of the dimensions in the group causes the function to work when the cycle or drill-down level changes.

Though a total<Company> qualifier in a table with only dimension Company is not really useful, it is valid syntax (you'll get the same result without the total qualifier in that case).

[#10]

"Though a total<Company> qualifier in a table with only dimension Company is not really useful, it is valid syntax (you'll get the same result without the total qualifier in that case)."

Sorry, my friend, that's not correct. Open my test file from above, clone the table, and put the suggested formula in one:

=

sum(aggr(if(LoginDate>=AddMonths(max(total <Company>  LoginDate),-12),1,0),LoginDate,Company))

and then put it WITHOUT the qualifiers "total" and "<Company>" in the Max function in the cloned table.I just did that, and got two different result sets in my tables. When I checked them, it is what I thought: without the "<company>" qualifier, the Max function picks the last login date of ALL companies in the table, not the company specified in the dimension. It then counts the logins back from that date, which means it is correct for the company that does have the maximum log in date, and correct for other companies who don't have login dates between (max date - 1 yr for all companies) and (max date - 1 yr for their companies), and incorrect for all other companies.

Note that if you create a list box with Company in it, and select a single company, you get the same results in both cases, because then Max for the whole table = Max for that company.

I don't know if this is intentional on QV's part, or an oversight, but I expected it to work as you suggested, and I've been banging my head against for some time because it manifestly doesn't.