Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a field [Enrollment Count] whose value is populated on daily basis.
Now in the middle of the month, I have to make a forecast for the remaining days of the month for which
I am using this expression :
if(not sum([Enrollment Count]),linest_m(total aggr(if(sum([Enrollment Count]),sum([Enrollment Count])), Date), Date)* Date +linest_b(total aggr(if(sum([Enrollment Count]),sum([Enrollment Count])),Date), Date))
This gives me a projection based on regression.
However, I also have a field state. If I select a particular state, then the projected values do not come on the chart.
Any particular reason for this?
Regards,
Rajiv
if(not sum([Enrollment Count]),
linest_m(total aggr(if(sum([Enrollment Count]),sum([Enrollment Count])), Date), Date)* Date
+linest_b(total aggr(if(sum([Enrollment Count]),sum([Enrollment Count])),Date), Date))
The expression that I am using.
If may be due to the forecast dates not being associated with any states. Once you select something only the associated data will be displayed - the "associative" experience. This is the default functionality of qlikview.
You can try concatenating the forecast data to your fact table for each state. This will allow you to select a state and still have the forecast dates be associated. If done correctly, your forecast will still display.
For example, you could do this
UniqueStates:
LOAD DISTINCT
STATES //Change to your actual state field name
RESIDENT #FactTabelName; //#FactTableName should be your table name
For i = 15 to 30 //From day 15 to day 30 - assuming 30 days
CONCATENATE (#FactTabelName) //#FactTableName should be your table name
LOAD
STATES //Change to your actual state field name
,MakeDate(2012,3,$(i)) as Date //creates date for each remaining day
RESIDENT UniqueStates;
Next