AVG(EXISTING()) syntax error

Question

I want to calculate sales for promotion using it's date. I need 3 measures, avg sales from 21 days before promotion start date, sales in between of promotion's start and end date, and sales from 21 days after promotion's end date. Why Visual Studio highlights avg in code below?

CREATE MEMBER CURRENTCUBE.[Measures].[Sales in promotion]
 AS Avg(Existing([Promotion].[Promotion name].[Promotion name]),[Measures].[Sales]), ...

Same in here:

CREATE MEMBER CURRENTCUBE.[Measures].[Sales before promotion]
 AS (EXISTING([Promotion].[Promotion name].[Promotion name]), AVG(strtomember("[Date].[Date].&["+ [Promotion].[Date].currentmember.member_key+"]").lag(21) : strtomember("[Date].[Date].&["+ [Promotion].[Date From].currentmember.member_key+"]"),
[Measures].[Sales])) ...

If I do sum(existing()) in first measure, the sum is calculated correctly, but it doesn't allow me to get average.

you just want the average per promotion ? so, for example, if there were 2 promotions in the period the answer should be (TotalSales/2) — whytheq

whytheq whytheq · Accepted Answer · 2016-10-06T07:29:51

EXISTING will only help if [Promotion] is part of your query in either the WHERE or SELECT clause. If it is not included in either of these clause then EXISTING will be finding 1 member - the All member.

You could try NonEmpty and maybe move the period logic into a custom set?

WITH
  SET [PERIOD] AS
     STRTOSET(
        "[Date].[Date].&["+ [Promotion].[Date].currentmember.member_key+"].lag(21) 
        : 
     [Date].[Date].&["+ [Promotion].[Date From].currentmember.member_key+"]"
     )

From the code you posted I cannot tell if you want a daily average or and average per promotion ? Say there were 2 promotions over the 21 days does this mean you want (Total/2/21) ?

AVG(EXISTING()) syntax error

1 Answers