Help with Missing values from a table

[Batgirl_93]

Hi,

I am relatively new to Qliksense and am really struggling with a table which compares Forecast Values to Sales Values. I have a version for the Previous day and for the month to date.

I am getting this

Category	Actuals	Forecast	v Forecast	%
Totals	£ 122,300	£ 138,785	-£ 16,485	-11.9%
Pharma	£ 100,000	£ 120,000	-£ 20,000	-16.7%
Petfood	£ 15,000	£ 12,000	£ 3,000	25.0%
Consumables	£ 7,000	£ 6,500	£ 500	7.7%
Equipment	£ 100	£ 135	-£ 35	-25.9%
Other	£ 200	£ 150	£ 50	33.3%

When I need to get

Category	Actuals	Forecast	v Forecast	%
Totals	£ 122,300	£ 140,285	-£ 17,985	-12.8%
Pharma	£ 100,000	£ 120,000	-£ 20,000	-16.7%
Petfood	£ 15,000	£ 12,000	£ 3,000	25.0%
Consumables	£ 7,000	£ 6,500	£ 500	7.7%
Instruments	£ 0	£ 1,500	-£ 1,500	-100.0%
Equipment	£ 100	£ 135	-£ 35	-25.9%
Other	£ 200	£ 150	£ 50	33.3%

If there are no Sales on the day for the category the Forecast number is not pulled in. 

In the Previous Day table I have the following Expressions for Actuals and Forecast

Actuals

{1<[Sales Invoice Financial Date]={"$(=Date(Max([Sales Invoice Financial Date])))"}>}

If(IsNull(SUM([Forecast Forecast (Net)])/sum([Forecast Forecast (Gross)])),
sum([Sales Invoice Actual]),
sum([Forecast Forecast (Net)])/sum([Forecast Forecast (Gross)])*sum([Sales Invoice Actual])
)

Forecast

{1<[Sales Invoice Financial Date]={"$(=Date(Max([Sales Invoice Financial Date])))"}>}

sum([Forecast Forecast (Net)])

Can anyone please advise what I am doing wrong?

[Pierrick]

Hello @Batgirl_93,

In fact, you sum up your sales with a filter based on the current date. If there are no sales for this category, it's normal that it's not displayed.

To counter this, you can add at the end of your formula : +sum(0)

As there's no date filter in this one, it will display all possible categories, while displaying 0 in value, which will have no impact on your results.

[Batgirl_93]

Hi Pierrick,

Thank you for your reply 😊

Adding the Sum(0) to the end of the Actuals formula does give me £0 for the Actuals but it also gives £0 for the Forecast as well when it should have a value pulled in. Do I need to change my set expression on that one as well to get the value pulled in?

I have tried adding the + sum(0), removing it, changing the Set Identifier and I still can't get the value to pull in 😕

[Pierrick]

Hello @Batgirl_93,

If you don't have a value displayed, it's because you don't have any data corresponding to your formula, I think.
Is your forecast in the same table as your sales?
Don't hesitate to attach an sample (qvf) to the subject so that we can help you more easily.

[Chanty4u]

Try this.forecast :

{1<[Sales Invoice Financial Date]={"$(=Date(Max([Sales Invoice Financial Date])))"}>}

SUM({1<[Sales Invoice Financial Date]={"$(=Date(Max([Sales Invoice Financial Date])))"}>}[Forecast Forecast (Net)]

)