Planning Analytics

Hi,
By default the subset editor does not remove duplicates.  In perspectives, if you use the set editor and choose hierarchy sort, it removes duplicates as long as the element does not show up in the hierarchy more than once.
In PAW/PAX subset editor you need to do a work around.  If you switch to MDX mode, and add the following it should get rid of duplicates:
INTERSECT( [dimension name].MEMBERS, {Rest of the existing MDX code } )
Another work around is to paste the elements into a new tab in excel and choose the remove duplicates.  Then copy it again and paste into the subset editor.

------------------------------
Scott Brown

Original Message:
Sent: Mon February 28, 2022 12:12 AM
From: Luiz gustavo Ribeiro
Subject: Removing duplicate elements when pasting in the Set editor in PAX

Hello,

When copying text from an excel sheet/quick report and pasting in the set editor we get duplicated elements if in the copied selection there are more than one occurrence of the same element. Is there an way to removing the duplicates in the set editor and keeping only one instance of each unique element? 

I could swear that this was a normal behavior before but now users are reporting that pasting in the set editor is generating a lot of duplicates of the same element. Thanks.

------------------------------
Luiz gustavo Ribeiro
------------------------------

Hi,
By default the subset editor does not remove duplicates.  In perspectives, if you use the set editor and choose hierarchy sort, it removes duplicates as long as the element does not show up in the hierarchy more than once.
In PAW/PAX subset editor you need to do a work around.  If you switch to MDX mode, and add the following it should get rid of duplicates:
INTERSECT( [dimension name].MEMBERS, {Rest of the existing MDX code } )
Another work around is to paste the elements into a new tab in excel and choose the remove duplicates.  Then copy it again and paste into the subset editor.

------------------------------
Scott Brown

Original Message:
Sent: Mon February 28, 2022 12:12 AM
From: Luiz gustavo Ribeiro
Subject: Removing duplicate elements when pasting in the Set editor in PAX

Hello,

When copying text from an excel sheet/quick report and pasting in the set editor we get duplicated elements if in the copied selection there are more than one occurrence of the same element. Is there an way to removing the duplicates in the set editor and keeping only one instance of each unique element? 

I could swear that this was a normal behavior before but now users are reporting that pasting in the set editor is generating a lot of duplicates of the same element. Thanks.

------------------------------
Luiz gustavo Ribeiro
------------------------------

Hi,
By default the subset editor does not remove duplicates.  In perspectives, if you use the set editor and choose hierarchy sort, it removes duplicates as long as the element does not show up in the hierarchy more than once.
In PAW/PAX subset editor you need to do a work around.  If you switch to MDX mode, and add the following it should get rid of duplicates:
INTERSECT( [dimension name].MEMBERS, {Rest of the existing MDX code } )
Another work around is to paste the elements into a new tab in excel and choose the remove duplicates.  Then copy it again and paste into the subset editor.

------------------------------
Scott Brown

Original Message:
Sent: Mon February 28, 2022 12:12 AM
From: Luiz gustavo Ribeiro
Subject: Removing duplicate elements when pasting in the Set editor in PAX

Hello,

When copying text from an excel sheet/quick report and pasting in the set editor we get duplicated elements if in the copied selection there are more than one occurrence of the same element. Is there an way to removing the duplicates in the set editor and keeping only one instance of each unique element? 

I could swear that this was a normal behavior before but now users are reporting that pasting in the set editor is generating a lot of duplicates of the same element. Thanks.

------------------------------
Luiz gustavo Ribeiro
------------------------------

Hi,
By default the subset editor does not remove duplicates.  In perspectives, if you use the set editor and choose hierarchy sort, it removes duplicates as long as the element does not show up in the hierarchy more than once.
In PAW/PAX subset editor you need to do a work around.  If you switch to MDX mode, and add the following it should get rid of duplicates:
INTERSECT( [dimension name].MEMBERS, {Rest of the existing MDX code } )
Another work around is to paste the elements into a new tab in excel and choose the remove duplicates.  Then copy it again and paste into the subset editor.

------------------------------
Scott Brown

Original Message:
Sent: Mon February 28, 2022 12:12 AM
From: Luiz gustavo Ribeiro
Subject: Removing duplicate elements when pasting in the Set editor in PAX

Hello,

When copying text from an excel sheet/quick report and pasting in the set editor we get duplicated elements if in the copied selection there are more than one occurrence of the same element. Is there an way to removing the duplicates in the set editor and keeping only one instance of each unique element? 

I could swear that this was a normal behavior before but now users are reporting that pasting in the set editor is generating a lot of duplicates of the same element. Thanks.

------------------------------
Luiz gustavo Ribeiro
------------------------------

Hi Wim,

That would be because you are passing two parameters, two sets in this case, to the Distinct function whereas Distinct only accepts one parameter of type set.
I'm guessing you started out with a set expression like:

{{[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]}}

which already is a  union and as such the same as:

Union({[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]}, ALL)

and had you just wrapped that expression with the Distinct function as in:

Distinct({{[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]}})

which therefore is the same as:

Distinct(Union({[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]}, ALL))

That would have been fine. Adding additional sets of curly braces is allowed but unnecessary and the execution engine will unfold it effectively.

But the real question I guess is why you are create a set per member here in the first place? What you really are asking for is:

Distinct({[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101], [Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]})

That's all that's needed.

Hope that helps!

Hi,
By default the subset editor does not remove duplicates.  In perspectives, if you use the set editor and choose hierarchy sort, it removes duplicates as long as the element does not show up in the hierarchy more than once.
In PAW/PAX subset editor you need to do a work around.  If you switch to MDX mode, and add the following it should get rid of duplicates:
INTERSECT( [dimension name].MEMBERS, {Rest of the existing MDX code } )
Another work around is to paste the elements into a new tab in excel and choose the remove duplicates.  Then copy it again and paste into the subset editor.

------------------------------
Scott Brown

Original Message:
Sent: Mon February 28, 2022 12:12 AM
From: Luiz gustavo Ribeiro
Subject: Removing duplicate elements when pasting in the Set editor in PAX

Hello,

When copying text from an excel sheet/quick report and pasting in the set editor we get duplicated elements if in the copied selection there are more than one occurrence of the same element. Is there an way to removing the duplicates in the set editor and keeping only one instance of each unique element? 

I could swear that this was a normal behavior before but now users are reporting that pasting in the set editor is generating a lot of duplicates of the same element. Thanks.

------------------------------
Luiz gustavo Ribeiro
------------------------------