Using Rangesum for YTD calc

[#1]

Hi Experts,

Could someone help me debug this expression:

sum(aggr(RangeSum(Above((Sum({$<[number]={'*SINV*'},[res_partner.name]={*}-{''},date_invoice={'>=$(=MonthStart(AddMonths(MonthYear),-(fMonth-1)))<$(=MonthEnd(MonthYear))'}>} [amount_untaxed])-

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

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

Thanks

Swetha

[Gysbert_Wassenaar]

,-(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.

[#3]

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

[Gysbert_Wassenaar]

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?

[#5]

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

[Gysbert_Wassenaar]

Have you read these?

Calculating rolling n-period totals, averages or other aggregations‌

The As-Of Table‌

These apply to Qlik Sense as well.

[#7]

I have tried the Range Sum Aggr combination, but that works when there is only one dimension. i have two dimensions (month and currency).

Thanks

Swetha

[Gysbert_Wassenaar]

Then try the asof table