Max Date Outside of Current Selection Where Spend > 0

[#1]

Hi,

Am trying to return the last date where spend > 0.

By using maxstring(CalYearMonth), returns the highest date value loaded in the db.

Now need to either include a "where" clause, or some sort of Set Analysis to return only when spend is greater than 0.

Also would like to evaluate the entire data set (and there for include those excluded by the current date selections).

Can this only be achieved by doing an "Indirect Set Analysis" (as am using QV v8.5 where this technique didn't exist)?

Please advise,

Kind regards,

Rich

[pat_agen]

Hi Rich,

it isn't too clear what you are trying to achieve.

are you trying to get a list of suppliers with the last date they sold something? so one supplier one maxdate? or is it something else.

why do you have any date selections in place?

[#3]

Hi Pat,

My aim is to identify suppliers that have been phased out over a period of time, and of these phased suppliers, identify which was the last period that had any spend.

Need to have date selection in place, as assumption is that every supplier at some point in time would report a spend figure (and also need to check whether "maverick" suppliers spend has discontinued over a period of time (and when the last purchase was))

Thanks to Matt C, have been able to return all suppliers that have no spend in a certain period: http://community.qlik.com/forums/t/43484.aspx)

Now would like to expand and return the last date these suppliers had any spend. My current attempts using Maxstring(CalYearMonth), will return the last loaded date (where spend can be 0 - which I need to ignore). So logically, if my list is returning all suppliers that have no spend reported within a time period, I now need to be looking outside of it.

-----------------------------------------------------------------------------------------------------------

For now, lets assume that the selected date range is always greater than the excluded

Being relatively new to QV, I can only see a few options to do this:

1) Set Analysis using {1} - which disregards the current selection

2) Indirect Set Analysis, using the "p()" and "e()" functions. However, this was only released in v9.0 and am using v8.5

Does anyone know how to do this without having to do the "Indirect Set Analysis"?

Please advise,

Kind regards,

Rich