July 12, 2018 at 7:28 am
Hi Friends,
I am trying to sum up two of the measures for Period to date calculation. I could not get the result as expected instead it gives me NULL. Is it possible to add two different measures like that? If I use any one of the measures I am able to get the result. But if I try to add, then result is NULL. The logic behind this is, there are two different measure groups. for Actual Sales and Projected Sales. So If fdmension filter "Include Projected Data" is used, then sum(Actual + Projected sales). I don't know what goes wrong. Any suggestions would be appreciated. Please find the code below.
SCOPE [Other Filters].[Projected Data].[Include Projected Data];
SCOPE (Descendants([Date].[Retail Hierarchy].[All], , AFTER));
[Measures].[Net Sales] = ([Date].[Retail Hierarchy].currentmember.prevmember, [MEASURES].[z Net Sales] + [MEASURES].[Projected z Net Sales] );
END SCOPE;
SCOPE ([Period].[Period To Date].[WTD], Descendants([Date].[Retail Hierarchy].[All], [Date].[Retail Hierarchy].[Retail Week], SELF_AND_AFTER));
[Measures].[Net Sales] = (ANCESTOR([Date].[Retail Hierarchy].currentmember,[Date].[Retail Hierarchy].[Retail Week]), [MEASURES].[z Net Sales] + [MEASURES].[Projected z Net Sales]);
END SCOPE;
SCOPE ([Period].[Period To Date].[MTD], Descendants([Date].[Retail Hierarchy].[All], [Date].[Retail Hierarchy].[Retail Month], SELF_AND_AFTER));
[Measures].[Net Sales] = (ANCESTOR([Date].[Retail Hierarchy].currentmember,[Date].[Retail Hierarchy].[Retail Month]), [MEASURES].[z Net Sales] + [MEASURES].[Projected z Net Sales]);
END SCOPE;
SCOPE ([Period].[Period To Date].[QTD], Descendants([Date].[Retail Hierarchy].[All], [Date].[Retail Hierarchy].[Retail Quarter], SELF_AND_AFTER));
[Measures].[Net Sales] = (ANCESTOR([Date].[Retail Hierarchy].currentmember,[Date].[Retail Hierarchy].[Retail Quarter]), [MEASURES].[z Net Sales] + [MEASURES].[Projected z Net Sales]);
END SCOPE;
SCOPE ([Period].[Period To Date].[YTD], Descendants([Date].[Retail Hierarchy].[All], [Date].[Retail Hierarchy].[Retail Year], SELF_AND_AFTER));
[Measures].[Net Sales] = (ANCESTOR([Date].[Retail Hierarchy].currentmember,[Date].[Retail Hierarchy].[Retail Year]), [MEASURES].[z Net Sales] + [MEASURES].[Projected z Net Sales]);
END SCOPE;
Thanks,
Charmer
July 12, 2018 at 7:36 am
two different measure groups. for Actual Sales and Projected Sales
Are the really two different measure groups or two different measure in the same measure group? Have you tried combining the two in a calculated measure and then using that measure in the formula?
July 12, 2018 at 8:01 am
They are two different measures from two different measure groups. Yes I tried creating a calculated measure and using that measure in the formula but it did not work.
CREATE MEMBER CURRENTCUBE.[MEASURES].[Projected Net Sales]
AS [MEASURES].[Net Sales] + [MEASURES].[Projected z Net Sales]
, VISIBLE = 0 ;
SCOPE [Other Filters].[Projected Data].[Include Projected Data];
--RETAIL HIERARCHY
SCOPE (Descendants([Date].[Retail Hierarchy].[All], , AFTER));
[Measures].[Net Sales] = ([Date].[Retail Hierarchy].currentmember.prevmember, [MEASURES].[Projected Net Sales]);
END SCOPE;
SCOPE ([Period].[Period To Date].[WTD], Descendants([Date].[Retail Hierarchy].[All], [Date].[Retail Hierarchy].[Retail Week], SELF_AND_AFTER));
[Measures].[Net Sales] = (ANCESTOR([Date].[Retail Hierarchy].currentmember,[Date].[Retail Hierarchy].[Retail Week]), [MEASURES].[Projected Net Sales] );
END SCOPE;
SCOPE ([Period].[Period To Date].[MTD], Descendants([Date].[Retail Hierarchy].[All], [Date].[Retail Hierarchy].[Retail Month], SELF_AND_AFTER));
[Measures].[Net Sales] = (ANCESTOR([Date].[Retail Hierarchy].currentmember,[Date].[Retail Hierarchy].[Retail Month]), [MEASURES].[Projected Net Sales]);
END SCOPE;
SCOPE ([Period].[Period To Date].[QTD], Descendants([Date].[Retail Hierarchy].[All], [Date].[Retail Hierarchy].[Retail Quarter], SELF_AND_AFTER));
[Measures].[Net Sales] = (ANCESTOR([Date].[Retail Hierarchy].currentmember,[Date].[Retail Hierarchy].[Retail Quarter]), [MEASURES].[Projected Net Sales] );
END SCOPE;
SCOPE ([Period].[Period To Date].[YTD], Descendants([Date].[Retail Hierarchy].[All], [Date].[Retail Hierarchy].[Retail Year], SELF_AND_AFTER));
[Measures].[Net Sales] = (ANCESTOR([Date].[Retail Hierarchy].currentmember,[Date].[Retail Hierarchy].[Retail Year]), [MEASURES].[Projected Net Sales]);
END SCOPE;
Thanks,
Charmer
July 12, 2018 at 8:07 am
I would suggest you start by creating a Net Sales 1 and a Net Sales 2, each with one half of the equation to see what's produced. Saying only a Null is produced isn't much help. If you do them in two components, are the both null, or is just one? That should help to start running down the issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply