Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]))
May be you need to add TOTAL here:
(Sum(TOTAL {<[fMonthStart]={"$(=vYesterdayMonthStart)"}>} [budgeted_sales])/1000) / (networkdays(monthstart(today()-1), monthend(today()-1)))
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;
May be you need to add TOTAL here:
(Sum(TOTAL {<[fMonthStart]={"$(=vYesterdayMonthStart)"}>} [budgeted_sales])/1000) / (networkdays(monthstart(today()-1), monthend(today()-1)))
Thanks Sunny, worked a treat!
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
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?
Try this
(Sum(TOTAL <Location> {<[fMonthStart]={"$(=vYesterdayMonthStart)"}>} [budgeted_sales])/1000) / (networkdays(monthstart(today()-1), monthend(today()-1)))
Thank you, that worked.
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