topic Re: Brain Failure!! in QlikView

Brain Failure!!

Tue, 17 Apr 2012 11:14:39 GMT

Hello All,

I am having brain failure. I need to make the below query dynamic and calculate values that fall within the months specified, so:

sum(if(MyDate>='01/04/2010' AND MyDate<'31/05/2011',MyValue)) - returns the correct value for today, hardcoded.

What I need to do is:

Add all my values together that fall within April 2010 to May 2011. Next month I will need it to be May 2010 - June 2011. And so on.

I can get the right Months using AddMonths(today(), -24) and AddMonths(today(),-11) respectively for the period that I am working with but my brain is failing on the if statement that says if(MyDate = AddMonths(etc) then sum MyValue.

If I get that working then SetAnalysis would be the next step. If you can suggest SetAnalysis upfront. Happy to go that route.

Thanks

Lee

Brain Failure!!

lironbaram — Tue, 17 Apr 2012 12:12:54 GMT

you can try set analysis like this:

sum({<MyDate={">=$(=monthstart(addmonths(today(),-24))) <=$(=monthend(addmonths(today(),-11))) "}>}MyValue))

Brain Failure!!

oxtontom — Tue, 17 Apr 2012 12:35:47 GMT

Hey Lee,

I like this little tool when creating complex set analysis. Just a way to help kickstart the brain

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx

Hope that helps!

Tom

Re: Brain Failure!!

Tue, 17 Apr 2012 12:45:48 GMT

Thanks Tom. That is a nice tool but I still can’t work out the expression that needs to be applied to MyDate.

Once I have that sussed, I will be using it!

Re: Brain Failure!!

CELAMBARASAN — Tue, 17 Apr 2012 12:49:54 GMT

Hi,

Try with this

Sum({<MyDate={">=$(=MonthStart(Today(),-24))<=$(=MonthEnd(Today(),-11))"}>} MyValue)

Celambarasan

Re: Brain Failure!!

Tue, 17 Apr 2012 13:03:48 GMT

Thx Liron and Celambarasan ... those must be close but both returning blank.

=sum(if(MonthStart(MyDate)>=MonthStart(AddMonths(today(),-24)) AND MonthStart(MyDate)<MonthStart(AddMonths(today(),-11)),MyValue))

That works. MonthStart was my brain failure.

Just need to convert that now into Set and jobs a good'un