topic Re: Min date doesn't work with Sum in QlikView

Min date doesn't work with Sum

Tue, 10 Sep 2013 08:33:42 GMT

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

Re: Min date doesn't work with Sum

swuehl — Tue, 10 Sep 2013 08:38:40 GMT

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))

Re: Min date doesn't work with Sum

Tue, 10 Sep 2013 08:45:13 GMT

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.

Re: Min date doesn't work with Sum

Tue, 10 Sep 2013 08:51:05 GMT

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?

Re: Min date doesn't work with Sum

swuehl — Tue, 10 Sep 2013 08:52:57 GMT

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

And your variable is also a number, right?

Re: Min date doesn't work with Sum

Peter_Cammaert — Tue, 10 Sep 2013 08:53:50 GMT

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

Re: Min date doesn't work with Sum

swuehl — Tue, 10 Sep 2013 08:56:34 GMT

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.

Re: Min date doesn't work with Sum

Tue, 10 Sep 2013 09:15:36 GMT

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

Re: Re: Min date doesn't work with Sum

Tue, 10 Sep 2013 09:20:00 GMT

Please find the attached QVW file for example

Re: Min date doesn't work with Sum

swuehl — Tue, 10 Sep 2013 09:29:18 GMT

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.

Re: Min date doesn't work with Sum

Tue, 10 Sep 2013 10:00:29 GMT

swuehl,

u made my day thank you very much.

Regards

Sameer