Min date doesn't work with Sum

[#1]

Hello All,

I am trying to calculate an indicator using minimum date. So i set a
variable called 'vPeriodStart' like this

vPeriodStart= min(uDAY) and it gives the earliest date back for a selected fiscal period. Now if i want to use this variable like this

sum(if(Opening > $(vPeriodStart)
or ClosingDate
< $(vPeriodStart) ,0,1)) it
does not work. I also tried

sum(if(Opening >min(uDAY) or ClosingDate < min(uDAY),0,1)) but same result it gives me no value back.

i also tried to format date in every
possible way with Date and Date# it does not work. I am confused why i am
unable to use Min(Date) function in sum formula.

now i if set the value of variable

vPeriodStart= today()

vPeriodStart='09.09.2013'

it works

Now i need to make it works with vPeriodStart= min(uDAY) I tried every possible way but it not working. I am wondering if anybody can help me this

Thnaks

[swuehl]

Have you defined your variable in variable overview with a leading equal sign or without? Try with an equal sign

=min(uDAY)

and check the result in a text box:

=vPeriodStart

then, if you get an expected result, try

sum(if(Opening > vPeriodStart
or ClosingDate
< vPeriodStart ,0,1))

[#3]

Hi,

I tried the = sign it does not work. I already tested the output of variable in a text box it works fine and it gives the minimum date back.

[#4]

Hi

I seem to think that this is with regards to the format of the returned value.

Put an Input box and list box on a sheet and compare the Opening  and $(vPeriodStart) values to each other.

If this doenst work, do you mind send a Qvw model? Might give a better picture of whats going wrong?

[swuehl]

I am assuming that your Opening and ClosingDate are QV dates, with a numeric representation, right?

And your variable is also a number, right?

[Peter_Cammaert]

Isn't this a case of nested aggregation? It certainly works when you set variable vPeriodStart in your load script. But then you loose Fiscal Period selection...

Peter 

[swuehl]

Peter,

it's not a nested aggregation when you define your variable with a leading equal sign, so the variable will be replaced by the result, not the function.

[#8]

I tried both numeric and Date representation both doesn't work

[#9]

Please find the attached QVW file for example

[swuehl · Accepted solution]

As said, you need to use a leading equal sign (which was missing in your app) in the variable definition and you need to create a number:

=num(min(uDAY))

in variable overview. Result of you expression is then 189.