7 Replies Latest reply: Apr 4, 2016 8:44 AM by Gysbert Wassenaar

# Using Rangesum for YTD calc

Hi Experts,

Could someone help me debug this expression:

I have created a master calendar and using that to calculate YTD.

Thanks

Swetha

• ###### Re: Using Rangesum for YTD calc
,-(fMonth-1)

fmonth-1

I assume these two are supposed to be references to the same field or variable. Make sure to use the exact case sensitive name. fMonth and fmonth are two different names.

Oh, and you can get rid of the AddMonths function. You're already using MonthStart and that accepts an offset parameter that works the same as the second parameter to AddMonths.

• ###### Re: Using Rangesum for YTD calc

Hi Gysbert,

Thanks for the response. My pivot table looks like this :

The user selects the month and year for which they want to see the year to date calculation. For the YTD calc this is the expression -

Sum({\$<[number]={'*SINV*'},[res_partner.name]={*}-{''}, [date_invoice]={'>=\$(=MonthStart(max(MonthYear),-(fMonth-1)))<\$(=MonthEnd(max(MonthYear)))'}>}amount_untaxed)-

Sum({\$<[number]={'*SREF*'},[res_partner.name]={*}-{''}, [date_invoice]={'>=\$(=MonthStart(max(MonthYear),-(fMonth-1)))<\$(=MonthEnd(max(MonthYear)))'}>}amount_untaxed)

The dimenion is Currency. Could you please let me know if i am working in the right direction to derive the YTD. Is there anything that i am missing here because this expression just refuses to give me the correct values rather it just doesn't give me a  cumulative value.

Thanks

Swetha

• ###### Re: Using Rangesum for YTD calc

That looks like a MTD expression to me. I'd expect YearStart to calculate a YTD:

Sum({\$<[number]={'*SINV*'},[res_partner.name]={*}-{''}, [date_invoice]={'>=\$(=YearStart(max(MonthYear),0,-(fMonth-1)))<\$(=MonthEnd(max(MonthYear)))'}>}amount_untaxed)-

Sum({\$<[number]={'*SREF*'},[res_partner.name]={*}-{''}, [date_invoice]={'>=\$(=YearStart(max(MonthYear),0,-(fMonth-1)))<\$(=MonthEnd(max(MonthYear)))'}>}amount_untaxed)

And I don't understand what fMonth is. Can you post a small qlik sense document that makes clear with with kind of data and dates your working?

• ###### Re: Using Rangesum for YTD calc

The fiscal year starts from Oct hence Jan 2016 YTD calculation should be Oct 2015 + Nov 2015 + Dec 2015 + Jan 2016. Hence i have a master calendar created for the same which contains dates as shown in the image:

For every invoice date there is a fMonth calculated which indicates how many months should be back tracked for YTD calculation. That is for example Jan 2016 the fMonth will be 4.

Thanks

Swetha