5 Replies Latest reply: Apr 17, 2012 9:03 AM by Lee Alderdice

# Brain Failure!!

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

you can try set analysis like this:

• ###### Re: Brain Failure!!

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

That works.  MonthStart was my brain failure.

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

• ###### Brain Failure!!

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

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

Hi,

Try with this

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

Celambarasan