Calculate Forecast

[#1]

Hello All,

I`m trying to calculate " Chargeback FORECAST" on a pivot table,

The Pivot table contains 3 "cyclic"

1. 1.grDims1
2. 2. grDims2
3. 3. grDims 3

Each cyclic contains the same dimensions- Account,acquire Bank, Biling company, product and so on..

Here is the calculation I`m trying to do:

Total chargeback by product, by country and by the other cyclic dimensions for the last 6 month,

divided by total sales, by product , by country and by the other cyclic dimensions for the last 6 month,

multiplied by total sales, by country and by the other cyclic dimensions (ignoring the "Product" dimension) for the last 6 month

divided by the number of days that hes passed this month until yesterday

multiplied by the number of days this month.

Here is an example of a calculation:

Total sales of product "Canada" in country "France" -100$

Total charge back of product "Canada" in country "France" -20$

Total sales of country "France" -200$

Calculation- total=20/100*200/11*30=109.09

something like the following:

=(Sum(Aggr(sum({<TranType={'Charge Back'}, CHB_Status={'CHB'}, DateInDay= ,DateInMonth= ,DateInYear= ,TransDay= ,TransMonth= ,TransYear= ,DateIn_Date={">=$(=AddMonths(Today(),-6))"}>}[Sum$])
,Product,BillingCountry,$(=GetCurrentField(grDims1)),$(=GetCurrentField(grDims2)),$(=GetCurrentField(grDims3)))))
/
(Sum(Aggr(Sum({<TranType={'Approved'}, TranStatus={'Regular'}, DateInDay= ,DateInMonth= ,DateInYear= ,TransDay= ,TransMonth= ,TransYear= ,DateIn_Date={">=$(=AddMonths(Today(),-6))"}>}[Sum$])
,Product,BillingCountry,$(=GetCurrentField(grDims1)),$(=GetCurrentField(grDims2)),$(=GetCurrentField(grDims3)))))
*(-1)
*
(Sum(aggr(sum({<TranType={'Approved'}, TranStatus={'Regular'}, DateInDay= ,DateInMonth= ,DateInYear= ,TransDay= ,TransMonth= ,TransYear= ,DateIn_Date = {">=$(=AddMonths(Today(),-6))"}>} [Sum$])
,BillingCountry,$(=GetCurrentField(grDims1)),$(=GetCurrentField(grDims2)),$(=GetCurrentField(grDims3))))
/ day(now()-1)
* round(num(Monthend(max(now()))) -Monthstart(min(now()))))

For some reason I don’t manage to get this to work, can someone please help?

Thanks in advance,

[swuehl]

It might be easier to help you if you can provide a small sample QVW to look at.

Have you tried splitting this long expression into smaller parts and check if each part returns what you are expecting?

Also check that the dollar sign expansions return your expected values.

If your dimension groups contain dimension values containing e.g. spaces, you should embed the returned dimension values in square brackets, like

=(Sum(Aggr(sum({<TranType={'Charge Back'}, CHB_Status={'CHB'}, DateInDay= ,DateInMonth= ,DateInYear= ,TransDay= ,TransMonth= ,TransYear= ,DateIn_Date={">=$(=AddMonths(Today(),-6))"}>}[Sum$])
,Product,BillingCountry,

[$(=GetCurrentField(grDims1))],[$(=GetCurrentField(grDims2))],[$(=GetCurrentField(grDims3))])))

[#3]

Hi  swuehi,

Thank you for your help

I attached the QVD file as an example.

The file contains part of a dimension that i think is relevant for the calculation.

I have tried to split the expression like you suggested and the result is the same.

I dont seem to manage to ignore the dimension, The first part of the calculation refers to 2 dimensions (Product and Billing Country) and cyclic group and the second part of the calculation refers to one dimension and cyclic group.

The goal of the calculation is to: the first part will calculate the CHB percentage by country and product and the second part will calculate the sales forecast only by country and it doesn't matter witch dimension the user  will select in the cyclic group

Thanks in advance,

[swuehl · Accepted solution]

It might be good to first look at the calculation broken down into parts, with fixed dimensions, not cyclic dimension groups. If the calculation works for different scenarios, we can think about how to add the dimension groups.

Attached a sample with two pivot tables with fixed dimensions.

Please check if the calculation is like you expect. To me, it does not look right with multiple dimensions on the detailed level.

[#5]

Hi,

Thanks again for your replay,

I don't completely understand what do you mean when you say "it does not look right with multiple dimensions on the detailed level."

In any case the forecast column looks OK to me, I have added on cyclic group column to the dimensions and added him also to the calculation of countrysales  (you can see on the attached file)

My question is:

If I choose on of  the cyclic dimension' such as the "account" dimension does the calculation in the countrysales column will always ignore the "product" dimension ?

[swuehl]

Since Product is part of your dimension group, and you list the current field within the total qualifier field list, I believe it won't be ignored when Product is selected from the dimension group.

[#7]

To be sure,

What if I take the product from the cyclic group, will it ignore the product dimension?

because I still have product in my table as a second dimension

[swuehl]

The key to ignore a dimension in your aggregation is the TOTAL qualifier.

If your qualifier field list doesn't show the product dimension, it should be ignored.

Sum(

TOTAL<BillingCountry,[$(=GetCurrentField(grDims1))]>

{<TranType={'Approved'}, TranStatus={'Regular'},

DateInDay= ,DateInMonth= ,DateInYear= ,DateIn_Date={">=$(=AddMonths(Today(),-6))"}>}

[Sum$])

[#9]

Thank you so much, you have been of a great help!