Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using Rangesum for YTD calc

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

7 Replies
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.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert,

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

Year_to_Date.png

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?


talk is cheap, supply exceeds demand
Not applicable
Author

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:

Master_Calendar.png

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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


talk is cheap, supply exceeds demand