YTD with a custom fiscal year

[#1]

Dear Experts,

I am working on a report which shows the monthly sales comparison of the current month of the current year vs the current month of the previous year (Example comparing – Feb 2015 and Feb 2016 in pivot tables) . The month selection is dynamic which lets the users choose which monthyear they want to compare. Along with this we also calculate the Year to Date(Example for Feb 2015 the year to date calculation should be from 01/10/2015 to 29/02/2016, since our fiscal year is from Oct to Oct).

The monthy comparison works just fine. However I am eternally stuck at YTD  calculation.

I have the below expression to calculate YTD:

Sum({$<[number]={'*SINV*'},[res_partner.name]={*}-{''},[date_invoice]={'>=$(YearStart(MonthStart($(MakeFinDate)),0,10))<=$(MonthEnd($(CurrMonth)))'}>}amount_untaxed)

Variable Definitions:

FinDate=’01/10/2000’

CurrMonth=Max([date_invoice])

MakeFinDate=SetDateYear($(FinDate),Year($(CurrMonth))-1)

If the user chooses the months as Feb 2015 and Feb 2016 for comparison then the Feb 2016 YTD should give me a cumulative amount starting from Oct 2015 to Feb 2016 but this expression does not give me that value, instead it just simply adds up the amount of Feb2015

and Feb 2016.

Can someone please verify the expression and throw some light as to where the issue must be. Many Thanks.

Note: I am a beginner in Qliksense hence my expression is not highly optimized and is very naive.

Thanks

Swetha

[Anonymous]

Did you check this?

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

[kaushiknsolanki]

Hi,

Try this

Sum({$<[number]={'*SINV*'},[res_partner.name]={*}-{''},[date_invoice]={'>=$(YearStart(CurrMonth,0,10))<=$(MonthEnd($(CurrMonth)))'}>}amount_untaxed)

Regards,

Kaushik Solanki

[Gysbert_Wassenaar]

I'm not sure your MakeFinDate will return the correct value. Perhaps this works:

Sum({$<[number]={'*SINV*'},[res_partner.name]={*}-{''},[date_invoice]={'>=$(YearStart($(CurrMonth),0,10))<=$(MonthEnd($(CurrMonth)))'}>}amount_untaxed)

[#5]

@Gysbert and @Kaushik, tried the expression but no luck.

Thanks

Swetha

[#6]

For accumulating the values, there are suggestions to use RangeSum function. Do you have an idea if that would work in this scenario. I am using a pivot table to display this data in qliksense.

Thanks

Swetha.

[Gysbert_Wassenaar]

Please post a small Qlik Sense app (or Qlikview document) that demonstrates the problem.

[#8]

Hi Gysbert,

Here is a qvf file with sample data. Basically I am using a pivot table to display the total untaxed amount of the months selected in the pivot month column and a straight table to display the YTD amount.

For example : if the months selected is Dec 2015 & Dec 2016, then the total untaxed amount For EUR should look like this as shown in the image.

For the YTD calculation, the fiscal year should start from Oct. For example, Dec 2016 YTD = Total of (Oct 2016 + Nov 2016 + Dec 2016) similarly Dec 2015 = Total of (Oct 2015 + Nov 2015 + Dec 2015).

If months chosen are Feb 2016 & Feb 2015 then Feb 2016 YTD = Total of (Oct 2015 + Nov 2015 + Dec 2015 + Jan 2016) similarly Feb 2015 = Total of (Oct 2014 + Nov 2014 + Dec 2014 + Jan 2015).

Any kind of pointers would be lot of help. Thanks.

Thanks

Swetha