locked
Calculated Mmber Aggregate Wildcard issues RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I have a few calculated members and adding a few attributes, howverevr i have a bad dimesnion that has M1,M2, M3 per year cycle. when 1 cycle the 3 month calc works fine but when multiple years the calc adds all finding

            THIS = AGGREGATE(
            (FILTER([Date Dimension].[Date SOP Month].Members,Instr([Date Dimension].[Date SOP Month].CurrentMember.Name,"M1",1) >= 1) --WILDCARD
            + FILTER([Date Dimension].[Date SOP Month].Members,Instr([Date Dimension].[Date SOP Month].CurrentMember.Name,"M2",1) >= 1)
            + FILTER([Date Dimension].[Date SOP Month].Members,Instr([Date Dimension].[Date SOP Month].CurrentMember.Name,"M3",1) >= 1)))/3;

    Is there a way i can split them per cycle wiithout specifying the cycle month (hardcoding) in calc

    Secondly, my attribute is messy for current month a M is assigned but now M shifts monthly and the drop down view of mONTH M is not easy to view or not unique (not 12 months but 12 months per year as the names are different). example: Jun 20 (M1), next month Cycle i will have Jun 20 (M). Its okay when in structure in filter pane but not in collumns list - any ideas?

    Thank yOU



    • Edited by Ismailc1 Monday, June 8, 2020 2:11 AM
    Monday, June 8, 2020 2:07 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    For the first, I think they are adding together, because in your filter you specified M1, M2 ,M3. For each year, there are all M1,M2 which will be filtered to be added together. We need to separate them. I haven't found modifications to to calculation, so that it can fit new request. Maybe we need to redesign it . Trying editing as [Date Dimension].[Date hierarchy].[Date Month]

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Ismailc1 Sunday, August 30, 2020 8:52 PM
    Tuesday, June 9, 2020 1:50 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    For the first, I think they are adding together, because in your filter you specified M1, M2 ,M3. For each year, there are all M1,M2 which will be filtered to be added together. We need to separate them. I haven't found modifications to to calculation, so that it can fit new request. Maybe we need to redesign it . Trying editing as [Date Dimension].[Date hierarchy].[Date Month]

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Ismailc1 Sunday, August 30, 2020 8:52 PM
    Tuesday, June 9, 2020 1:50 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, Thank you

    Its in a structure in the filter pane but the month attribute is in the columnsi want the structure in filter area as the structure exists of Cycle Year > Cycle Period > SOP Period

    ?

    regards  

    Wednesday, June 10, 2020 7:40 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi, any update on the caculation, perhaps i should add in a strcuture and referr to the structure in caculation
    Wednesday, June 10, 2020 7:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    What structure are you referring here ?  could you elaborate  a bit ?

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 12, 2020 9:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    The SOP Date is part of Date Structure Cycle Year > Cycle Peiod > SOP Period (in filter pane)

    and SOP Period on its own for column pane.

    I understand what you refering to but would i need to duplicate the structure to have it available in both filter & columns panes ?

    Saturday, June 13, 2020 4:28 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    Yes, you could do so, and it works well.

    But you also could use the filter in Column Label drop down list, it works also well for column filter.

    Lukas

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 17, 2020 2:12 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Is there a way i can duplicate the structure and keep the same name, im concerned users are going to be confused with 2 date structures?

    regards

    Thursday, June 18, 2020 6:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Either they are in different dimension, or they have different attribute name.

    We could not have exact same attribute names in one dimension.

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 19, 2020 7:55 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Apologies, i havent gotten back to this. will do next week.

    regards

    Friday, July 3, 2020 10:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank You very much, this worked. Inserted the calculation within the MDX
    Sunday, August 30, 2020 8:52 PM