Sum Aggr Set Analysis

[#1]

Hi All

New to the forums and hopefully someone could give me a bit of help

I have 6 fact tables that are all linked to a Link Table. I have to calculate the value on Quotes. If the quote dont have a Invoice the current rate should be used (This was joined onto the table), if however the Quote has a Invoice it should use the Invoice rate and calculate the price on the quote.

As they work with multiple currencies it is important that the rate gets divided by the price.

The formula looks like follows and works correctly to display the previous years  Quote values IF NO SELECTIONS ARE MADE.

sum({1<[DateeReq Year] = {$(veReqPrevYear)}>}

       (aggr(

               (if(IsNull(SuppInvDocNo),

                OnlineReqPrice / OnlineReqExRate,

                OnlineReqPrice / SuppInvRoe1))

      * OnlineReqQty,

      [DateeReq Year],OnlineReqCode)))

As soon as a selection of the DaeeReq Year is made the value becomes 0. I have been struggling on this for to long and if someone could help I would really appreciate it.

[lironbaram]

hi some times is better to put the set analysis in the inner parts of the aggr function

so something like

sum({1<[DateeReq Year] = {$(veReqPrevYear)}>}

       (aggr(

               (if(IsNull(only({1<[DateeReq Year] = {$(veReqPrevYear)}>}SuppInvDocNo)),

                avg({1<[DateeReq Year] = {$(veReqPrevYear)}>}OnlineReqPrice / OnlineReqExRate),

               avg({1<[DateeReq Year] = {$(veReqPrevYear)}>} OnlineReqPrice / SuppInvRoe1)))

      * sum({1<[DateeReq Year] = {$(veReqPrevYear)}>}OnlineReqQty,)

      [DateeReq Year],OnlineReqCode)))

[#3]

I tried that. What happens then is that it duplicates the Sum and the values turn out 10 times more than the original value. The formula as it is currently is the only way I could have gotten it to actually show the previous years values. It just don't want to work when a selection is made....

[lironbaram]

could you post sample data

[#5]

Sorry cant post sample data ☹

The dataset is to large as it ties up with all 7 facts that don't relate unless I put at least 100k records from each fact.