Calculating MTD values

[#1]

I need to calculate MTD sales. I think am having trouble in date formats. e.g. when I put =sum({$<Monthnum= {'8'}, Y = {'2012'}>} sales) as expression it gives me MTD sales but when i try to make it dynamic by doing =sum({$<Monthnum= {'month(today())'}, Y = {'year(Today())'}>} sales), it gives me 0. Monthnum contains numbers 1 to 12 and Y is 2010, 2011,...2013. I am new to qlikview, I am open to any other method to calculate MTD. Also, let me know if you need more information.

[MayilVahanan · Accepted solution]

Hi

     =Sum({<Monthnum = {$(Month(today())*1)},Y = {$(Year(Today()))}>}Sales)

Hope it helps

[#3]

Thanks, Mayil. It still returns zero.

[MayilVahanan]

Hi

      =Sum({<Monthnum = {$(=Month(today())*1)},Y = {$(=Year(Today()))}>}Sales)

Hope it helps

[#5]

This worked. How to learn this tricks? Is there any material out there? Also, now as I wanted to do QTD I think its better to use conditions on date rather then month or year. So i tried

=Sum({<Date = {$(>=MonthStart(today()))},Date = {$(<=Today())}>}Sales)

This gives me 0 again. Do you see any changes that i need to make for it to work?

Thanks a lot for your response.

[#6]

See attached. It may help.

[#7]

Hi Samarth,

You could add a variable for the Quarter to date - ie. let vQToDate = Ceil(Month(DATE(today()))/3);

[#8]

Thank you Beanz. How do i use that variable to get total sales QTD?