Min and max on date not working

[#1]

Hi,

Could you please tell me why these expressions are not working?

Sum({$<business_date ={'>Date($(vMinDate))<Date($(vMaxDate))' >} Assets)

Sum({$<business_date ={'>$(vMinDate)<$(vMaxDate)' >} Assets)

the value of business_date is 2012-01-24 and I have statically made the values of vMinDate andvMaxDate variable in the same format?

I would actually like to subtract 1 from the vMinDate and then do the sum as given above. But before that the above expression should work correctly.

I also tried =Sum({$<business_date = {'>=Min(business_date)<Max(business_date)'}>}assets) but does not work either.

what am I doing wrong. Please let me know?

Thanks

Raghu

[#2]

Hi,

When referencing variables in Set Analysis, you must use a hash. Try the following expression.

SUM( {$<business_date = {">$(#vMinDate) <$(#vMaxdate)"} >} Assets)

The reason Sum({$<business_date = {'>=Min(business_date)<Max(business_date)'}>}  didnt work for you is becasue MIN() or MAX() return a number and not a date so the Set Modifier doesnt match the format of the business_date field. Just wrap the date function around the MIN and MAX and it should work.

Regards

Haider

[#3]

Try using double quotes instead of single quotes. Or prepare and send a sample document to play with.

[jagan]

Hi Raghu,

May be the Date formats for business_date and Date($(vMinDate)) is different.  Check this.

Or

you arrive date in number format by using Num(), so that it would be easier in set analysis.

if business_datenum is date field in numeric format then the expression is

=Sum({$<business_datenum={'>$(=vMinDate)<$(=vMaxDate)' >} Assets)

Hope this helps you.

Regards,

Jagan.

[#5]

Hello Haider,

This expression SUM( {$<business_date = {">$(#vMinDate) <$(#vMaxdate)"} >} Assets)

does not seem to work. It keeps giving me 0 for all range selection.

I just tried SUM( {$<business_date = {">$(#vMinDate)"} >} Assets) and does not work either.

Pls help.

Thanks

Raghu

[#6]

Hi Jagan,

Here is one sample QVW file attached. I ahve converted the business_date in load script to numeric. This is because as suggested on one of the posts above, that the min(business_date) and max(business_date) functions return numeric values.

I want to sum up all the values of Sales, for a selected data range. The only point to be noted is that the start date of my sales sum is 1 day before the min day of the select date range. So you will notice a -1 being done in the text box expression. so if user select dates from 4-14th Dec, my sum needs to be for 3-14th dec.

I am also attaching the excel file that I create to load the data in QVW.

Not sure why this is not working.

Thanks

Raghu

[#7 · Accepted solution]

Hi raghuvansh,

     This expression should work for you:

=Sum({$<business_date = {">=$(=Min(business_date)-1)<=$(=Max(business_date))"}>} Sales)

JG

[#8]

Hey Juan,

This works perfectly. The difference in my and yours formula is Syntax as I can see. Does it mean I was using incorrect syntax only?

Can you share some best practices on the syntax or general guidelines so that I can take care in future pls?

Thanks

Raghu

[#9]

Yes, I have changed only the expression.

Try with this document http://community.qlik.com/docs/DOC-1867: It has the basics about Set Analysis and Dollar Sign Expansion.

JG