Set Analysis - use of max date

[#1]

I am trying to sum up a unit field for a give/slected period. I can calculate the max(PODate) fine, but when I add the previous max function (i.e. max(PODate,2), it will not compute. I can even hard code a date in there, but it won't compute a function of summing up units for PODates less than a given date.

But, when I use the max(PODate,2) in a text box, it comes up with the correct date to use. Not sure why it is not calculating in the set analysis????

I have attached my qvd which is a pretty basic table.

[#2]

Can you explain what you are trying to achieve. Do you want something like the Revenue for the date = Max(date) - 1?

[stephencredmond]

Hi,

Your set was not right - unfortunately the syntax can make it difficult to follow.

This may work:

=sum({$ < PODate = {"$(=max(PODate,2))"} >} po_line.unit_price)

However, you will note that if you select only one date that max(PODate,2) will return Null (because there is no second from max if you only select one).

Is this really what you want?

Regards,

Stephen

[#4]

You are correct in that it is not what I really want. I am having trouble with getting the next date for an item if the user selects a date. For instance, the last price of a unit. If you select August, I have not problem getting the unit price for that time, but I'm not sure how to get the last unit price. I can't use the maximum minus 1 becuase the last instance of the price for each item could be different (i.e. one month, two months, etc.). So if I select August, and the last time I bought item 1 was three months ago, and the last time I bought item 2 was two months ago, how do I get those values?

I was hoping the max(PODate,2) would give me the next maximum date for each unit regardless of how long ago, but I can't figure out the formula for getting it to recognize the date range when only one date is selected.

[stephencredmond]

Hi,

It would be fairly simple to order your pricing in the load script and then load the Previous(Price) As LastPrice. Then you would always have it.

Stephen

[#6]

That would work for the current month but not for previous months. They are trying to evaluate based on the month selected. I recommended a spark graph showing the price over time, but they still want to evaluate from a selected month. I am having a hard time summing up fields based on a range of dates given a selected month, especially if they want the last price from a selected month.

I now think I understand the max function better and tried

=

sum({$ < PODate = {"$(=max({1} PODate,2))"} >} Unitprice)

this will give me all of the dates, but I can't understand how to get it to evaluate a period of all dates less than the selected date.

[#7 · Accepted solution]

I finally got the function to work. Looks like the double quotes that I was using should have been single quotes (even though all the help shows it in double quotes). Below is what finally worked for 9.0.

=sum({<InvDate = {'>=$(=date(min(InvDate))) <=$(=max({1} InvDate))'} >} Issues)

Thanks for all the help - they all helped me to piece it together...