Need to calculate Overall based on Date

[#1]

Hi,

I had a formula for Actuals- Forecast for each and every product based on some time restrictions from Date. When I select on Brand level, the overall value is not matching. But when i click on each and every product the value is correct.

For overall, the calculation should be based on date for every Product level.

Eg:

Brand	Product	Date	Actuals-Jan	Actuals - Feb	Actuals-March	Forecast-Jan	Forecast- Feb	Forecast-Mar	Formula(Actuals - Forecast)/sum(actuals)
B1	P1	1/1/2016	250	260	280	100	150	50	260/2719 = 0.0956
B1	P2	2/1/2016	788	789	630	250	320	411	688/2585=0.2661
B1	P3	3/1/2016	266	366	260	320	230	180

the formula is calculated like if the Date of the product is Jan (Actuals and Forecast will be calculated for first two months). Eg. Jan and Feb is considered. Date of the product is Feb, Actuals and Forecast taken from Feb and March.

sum(actuals)= date of product is Jan then Jan and Feb whole actuals for all the products.

In my formula, I used the set Analysis for calculating the first two months based on the Date of Product and the Numerator is aggregated based on Primary key Brand_Product.

When I click on Product, I am able see the value correctly for each and every product. But when I click on Brand, overall should be 0.3617, but it was showing some different value.

I couldn't get how to calculate the numerator based on the Date so that while aggregating it should show the correct value for overall

[Gysbert_Wassenaar]

Please post a small qlikview document that demonstrates the problem

[makrishnaraj]

Hi,

the issue could be because of the total mode in the expression. try changing the total mode from default 'Expression Total' to Sum or average.

hope this will resolve your issue

[#4 · Accepted solution]

The issue is resolved by creating flag in the datamodel for the restrictions of date. Then the overall calculation is achieved without any issue.