topic Re: Calculating MTD values in QlikView

Calculating MTD values

Thu, 02 Aug 2012 17:49:15 GMT

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.

Re: Calculating MTD values

MayilVahanan — Thu, 02 Aug 2012 18:05:06 GMT

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

Hope it helps

Re: Calculating MTD values

Thu, 02 Aug 2012 19:04:32 GMT

Thanks, Mayil. It still returns zero.

Re: Calculating MTD values

MayilVahanan — Thu, 02 Aug 2012 19:37:42 GMT

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

Hope it helps

Re: Calculating MTD values

Thu, 02 Aug 2012 20:48:23 GMT

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.

Re: Calculating MTD values

Thu, 02 Aug 2012 20:49:41 GMT

See attached. It may help.

Re: Calculating MTD values

Thu, 02 Aug 2012 20:56:57 GMT

Hi Samarth,

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

Re: Calculating MTD values

Thu, 02 Aug 2012 21:22:29 GMT

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