Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

parks399
New Contributor II

How to calculate fiscal period sales to date, similar to MTD?

I have sales by invoice date in my model.  For every line item, i have the invoice date, fiscal period work day number, period days,and many other attributes.  I need help with calculating accumulated sales by day. 

This is the formula that I started.  I'm also showing the sample table and results desierd.  I am intending to add a combo chart showing the accumulated sales by day in a fiscal period.  I appreciate you help with fixing this formula.

Sum({$<YEAR_PER={'2018_001',PER_WD={">=0 <=$(=Num(Max(PER_WD)))"}>}SALES)

Example Table: 

YEAR_PERINV_DATEPER_WDPER_DAYSSALES
2018_0011/1/201802410
2018_0011/2/2018124100
2018_0011/3/2018224100
2018_0011/4/2018324100

Mock Results needed:

  

PER_WDPER_To_Date
010
1110
2210
3310
8 Replies
lnimeshkrish91
New Contributor

Re: How to calculate fiscal period sales to date, similar to MTD?

Try this.

Rgds,

Nimesh Krishnan L

arvind1494
Contributor III

Re: How to calculate fiscal period sales to date, similar to MTD?

create variable as :

vF_Current_Year

=num(if(month(max(INV_DATE))<4,YearStart(YearStart(max(INV_DATE,-1)),0,4),YearStart(max(INV_DATE),0,4)))

Today = num(max(INV_DATE))

then use following expression for financial YTD:

sum({<INV_DATE={">=$(vF_Current_Year)<=$(Today)"}>}Sales)

MVP
MVP

Re: How to calculate fiscal period sales to date, similar to MTD?

I think what you need is RangeSum(Above(...)) function rather than using set analysis. Try this with PER_WD as dimension....

RangeSum(Above(Sum(SALES), 0, RowNo()))

parks399
New Contributor II

Re: How to calculate fiscal period sales to date, similar to MTD?

I think this is really close to it perfect now.  This is really exciting.  How do I limit the days to the max invoice day for the period.  Right now I am showing 24 days in the period, but we are in day 8 of sales.  The graph is showing days 9 through 24 with same total as day 8.  Can you share how to limit the days? Super thank you for the help so far.

MVP
MVP

Re: How to calculate fiscal period sales to date, similar to MTD?

May be this

If(Sum(Sales) <> 0, RangeSum(Above(Sum(SALES), 0, RowNo())))

parks399
New Contributor II

Re: How to calculate fiscal period sales to date, similar to MTD?

This works!!! Amazing!

Any advice you can give on how best to learn how to write these type of formulas? 

MVP
MVP

Re: How to calculate fiscal period sales to date, similar to MTD?

The best way to learn is to keep trying out different things. Help others on community and you will get to see different ways to approach the same problem and you will learn. For example, you could have solved the above problem using The As-Of Table approach also, but this requires intervention in the script.

omarbensalem
Esteemed Contributor

Re: How to calculate fiscal period sales to date, similar to MTD?

I agree.

The best way to learn is to be active in the community and try to help people as often as you can.