Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

5 Replies
lironbaram
Honored Contributor II

Brain Failure!!

you can try set analysis like this:

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

oxtontom
New Contributor III

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

Not applicable

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

Highlighted
Not applicable

Re: Brain Failure!!

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

Community Browser