6 Replies Latest reply on Nov 14, 2018 4:27 AM by seif saleh

# Grouping multiple dimension values

Hi,

I struggling to find a solution for the below, would really appreciate some help on this

I have the following table:

IDSub Funnel GroupSub funnel start dateSub funnel End Date
Funnel Group
1Initial Screening 1/1/20171/2/2017Applied
1Interview1/2/20171/3/2017Screened
1Approved1/3/20171/4/2017Screened
2Initial Screening 1/1/20171/2/2017Applied
2Rejected
 1/2/2017
1/4/2017Screened
3Initial Screening
 1/1/2017
Applied

I am trying to create new dimension values based on the following:

if Sub Funnel Group = "Initial Screening"  and Sub Funnel Group = " Approved"  then new dimension = "Approved"  ( ID = 1)

IF Sub Funnel Group = "Initial Screening"  and Sub Funnel Group = " Rejected" then new dimension = "Rejected" (ID = 2)

if sub funnel end date is null then new dimension = Funnel Group Value (ID = 3)

I am also trying to map the results to the funnel group, but the issue is that I will have duplicate values of the new calculated dimension for the ID

I am trying to have an end result which looks like this:

• ###### 1. Re: Grouping multiple dimension values

Hi, Seif

Without seeing a sample workbook, I just can make up the calculation based on your description, but can't test it

Below is the calculation you may have a try

if isnull({Fixed [id]:max([Sub funnel End Date])}) Then

[Funnel Group]

elseif (Not isnull({Fixed [id]:max(if [Sub funnel Group] = 'Initial Screening' then [Sub funnel Group] end)}))

and

(Not isnull({Fixed [id]:max(if [Sub funnel Group] = 'Approved' then [Sub funnel Group] end)})) then

'Approved'

elseif (Not isnull({Fixed [id]:max(if [Sub funnel Group] = 'Initial Screening' then [Sub funnel Group] end)}))

and

(Not isnull({Fixed [id]:max(if [Sub funnel Group] = 'Rejected' then [Sub funnel Group] end)})) then

'Rejected'

end

Hope this helps

ZZ

• ###### 2. Re: Grouping multiple dimension values

Hi Zhouyi,

I am also trying to mark the latest Sub funnel Group for each ID and Funnel Group

For example:

ID 1:

Initial Screening is the final step in Applied Funnel Step

Approved is the final step in Screened Funnel Step

Thank you so much for your help

• ###### 3. Re: Grouping multiple dimension values

Hi, Seif

I am glad to help. if you could make up some dummy data in a workbook, and explain the expected result, this will be easier for me to provide help

ZZ

• ###### 4. Re: Grouping multiple dimension values

Hi Zhouyi,

Thanks some much for your help

The expected outcome is the following:

1. The user selects a time frame.
2. If the sub funnel start data is in this frame and the subfunnel name is "1 - Applied" and if there are no other subfunnel steps (within the funnel group) for each user; then this will be called "live".
3. If the sub funnel start data is in this frame and the subfunnel name is "1 - Applied" and if there are other subfunnel steps (within the funnel group) for each user; then this row should be filtered out.

Thank you !!

• ###### 5. Re: Grouping multiple dimension values

Hi, Seif

thanks for your workbook. I am bit of confused that in your sample data, I didn't find any cases that have a sub funnel name with '1-Applied' only as highlighted below.

Is something I mis-understand?

ZZ

• ###### 6. Re: Grouping multiple dimension values

Hi Zhang,

For example candidate 15 has only 1-Applied' in the subfunnel linked to Applied funnel group and no other sub funnel steps linked to applied

for candidate 5

We have 1-Applied' in the subfunnel linked to Applied funnel group and rejected app linked to applied, so: the row with 1-Applied will be filtered out and only the row with rejected app will remain.

Hope this is clear.

Please let me know if you have any questions. I really appreciate your help

Seif