Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
you can try set analysis like this:
sum({<MyDate={">=$(=monthstart(addmonths(today(),-24))) <=$(=monthend(addmonths(today(),-11))) "}>}MyValue))
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
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!
Hi,
Try with this
Sum({<MyDate={">=$(=MonthStart(Today(),-24))<=$(=MonthEnd(Today(),-11))"}>} MyValue)
Celambarasan
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