Trying to spread figure from one day across a month

[#1]

You can see from my data below, a 'budget' number has been inserted on a single day of the month.  I want to spread this number across all days in the month but cannot seem to get it to work.  Does anyone have any suggestions?  Will I need to go back to the data?

This is the formula I have tried:

(sum({<[fMonthStart]={"$(=vYesterdayMonthStart)"}>} [budgeted_sales])/1000) / (networkdays(monthstart(today()-1), monthend(today()-1)))

where vYesterdayMonthStart = MonthStart(max([Invoice Date]))

[maxgro]

This is an example in the script

// test data with a budget on the month start

Fact:

load

       MonthStart(Date) as MonthStart,

       *,

       if(Date=MonthStart(Date), rand()*100*30) as BdgVal;

load

       Date(MakeDate(2015) + IterNo() -1)  as Date,

       rand()*100 as Val

AutoGenerate 1

While IterNo() <= 365;

// calc the budget in every date

Left Join (Fact)

LOAD

       MonthStart,

       Sum(BdgVal) / (max(Date) - min(Date)+1) as BdgValXDate

Resident Fact

Group By MonthStart;

[sunny_talwar · Accepted solution]

May be you need to add TOTAL here:

(Sum(TOTAL {<[fMonthStart]={"$(=vYesterdayMonthStart)"}>} [budgeted_sales])/1000) / (networkdays(monthstart(today()-1), monthend(today()-1)))

[#4]

Thanks Sunny, worked a treat!

[sunny_talwar]

Great, if you got what you wanted, please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[#6]

I spoke too soon.  The 'Total' is adding up the total for everything and I needed it by Location (which wasn't on my original screen print).  Is the 'Total' like SQL where you can use a 'For'? Like Total xxx for Location?

[sunny_talwar]

Try this

(Sum(TOTAL <Location> {<[fMonthStart]={"$(=vYesterdayMonthStart)"}>} [budgeted_sales])/1000) / (networkdays(monthstart(today()-1), monthend(today()-1)))

[#8]

Thank you, that worked.

[sunny_talwar]

Great.... if I may ask, would you be able to mark the appropriate response as correct and/or helpful. Marking it is assumed answer may not be correct if you got an answer you can safely mark as correct response. You don't have to, but I will ask you to reconsider this as this may help others in the future when they come to this thread for answers

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks,

Sunny