Repeat column Values

[former_member334960]

Hi All,

I have a requirement to create a new column with the condition as below :

1.png 2.png

In the new column, When the Actual Category is blank, it has to pick the First Review Category value and when the Actual review category has a value in it, then the new column should have those values until it is next changed.(the rows below should also have the same value)

In the example below, we have first few lines as blank under Actual Review Category, then we are showing New column as Expedited. Then We have Full Committee under Actual Review Category field, hence the new column should have "Full committee" now until it changes to the next state.

This way, we need to populate a new column. Attached is the snapshot of webi report with two different submission numbers.

Can some one please help on fixing this.

Thank you

Poojitha

[former_member334960]

Hi Swapnil.

With this logic we are able to only populate the right value for that one line, we are not able to populate for the below ones.

Full Committee should repeat until it is next changed to Expedited and Expedited should

[former_member363893]

Hello Poojitha,

I will check and get back to you.

Regards,

Swapnil

[ayman_salem · Accepted solution]

Here is the corrected solution:

- Sort the "Workflow Status Date" column with "Submission Number; Workflow Status Date"

- Define the following variable:

NewClumn: =If Not(IsNull([Actual Review Category])) Then [Actual Review Category] Else (If (IsNull(Previous(Self)) Or [Submission Number] <> Previous([Submission Number]) ) Then [First Review Category] Else Previous(Self) )

...

hope this solves your requirement

[former_member334960 · Accepted solution]

Hi Ayman,

I get multivalue error.

The actual review category field in the report here is a measure and note a dimension. Can you pls suggest from here.

[ayman_salem · Accepted solution]

Why is it a measure !!? In either case, try defining a new variable for the [Actual Review Category] dimension and insert it into the above formula to see if that solves the problem.

But I think the problem lies with your data or the construct of your table

[former_member334960 · Accepted solution]

I tried to create a new variable for [Actual Review Category] dimension but I still get the same error.

Note that I get error only for the ones where [Actual Review Category] and below rows

Screenshot of the measure used for Actual review category

[ayman_salem · Accepted solution]

Since this column consists of a formula, try to correct this formula with, for example, add (in [Submission Number] or forEach).

Also check how the [min number of days] was calculated ....

I can suggest that as I don't have an example of your raw data without aggregation.

[former_member334960 · Accepted solution]

I tried to add in submission/for each submission to the formula but the data is not coming up correctly. Stil I see some issues

As you do not have raw data, I will have try to check, Thank you for the suggestion

[former_member334960 · Accepted solution]

Solution worked. Thank you Ayman.