Brain Failure!!

[#1]

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

[lironbaram]

you can try set analysis like this:

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

[oxtontom]

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

[#4]

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!

[CELAMBARASAN]

Hi,

     Try with this

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

Celambarasan

[#6]

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