10 Replies Latest reply on Apr 24, 2018 7:31 AM by Okechukwu Ossai

# How to include particular period in IF Condition

Hi,

Currently, Multiple periods are selected in the filter Mark, I want to show all the periods but the "Calculation 3" expression based on Period="YTD_CY"

How to make this condition on a particular period,

• ###### 1. Re: How to include particular period in IF Condition

Hi Darshan,

Filters cannot be used in calculated fields,instead use parameters with iif condition.check the link.

thanks,

bharat

• ###### 2. Re: How to include particular period in IF Condition

Hi Darshan,

Not sure I understand the question fully. However, if you want to add Period as a condition, you can modify 'Calculation3' like this;

IF ATTR([Period]) = "YTD_CY" AND SUM([Wd (Max)]) <= AVG ([Avg of X]) AND SUM([Velocity]) <= AVG ([Avg of Y]) THEN 'OPTIMIZED'

ELSEIF ATTR([Period]) = "YTD_CY" AND SUM([Wd (Max)]) > AVG ([Avg of X]) AND SUM([Velocity]) <= AVG ([Avg of Y]) THEN 'LAGGARDS'

ELSEIF ATTR([Period]) = "YTD_CY" AND SUM([Wd (Max)]) <= AVG ([Avg of X]) AND SUM([Velocity]) > AVG ([Avg of Y]) THEN 'HIDDEN GEMS'

ELSEIF ATTR([Period]) = "YTD_CY" AND SUM([Wd (Max)]) > AVG ([Avg of X]) AND SUM([Velocity]) > AVG ([Avg of Y]) THEN 'POWER SKUs'

ELSE 'ERROR'

END

Hope this helps.

Ossai

• ###### 3. Re: How to include particular period in IF Condition

Hi Ossai,

Thank you for the quick response,

Yes, the solution that you have provided is giving me the required output but somehow the rest of the columns are not visible.

Current output: Months are not present in the output

Required output: Monthly data columns also required(M1,M2 etc.) but the condition should be on Period="YTD_CY"

• ###### 4. Re: How to include particular period in IF Condition

Thank you Bharat for the suggestion,

I have tried to change period into Parameter but using that im not able to create a proper condition.

let me know your thoughts regarding the same.

• ###### 5. Re: How to include particular period in IF Condition

You didn't attach any sample workbook, so it's difficult understanding your dataset and requirements. Can you explain more what you mean by 'month are not present in the output'? It will be helpful if you can give an example of where month is not present and the expected result. How is month related to other fields and filters used in your view?

• ###### 6. Re: How to include particular period in IF Condition

Oh, I can see that M9, M8 etc are all part of the Period field including YTD_CY.

I can modify my previous solution if you tell me what you want to happen when period is a month and when period is YTD_CY?

• ###### 7. Re: How to include particular period in IF Condition

I am getting the output based on filtering SKU on the 'YTD_CY' however, in that case, the individual month columns are not been reflected here as in the second screenshot (highlighted in yellow e.g. M9 M8 M7...... M1).

I shall require the same output along with the individual month columns while the SKU filter is based on 'YTD_CY'.

Please find attached the TBWX file.

• ###### 8. Re: How to include particular period in IF Condition

One approach will be to use LOD.

The inner {Fixed} LOD tries to calculate SKU Quadrant at the finest level of detail while the outer {Fixed} LOD  rolls it up to the level of detail on your view. I've attempted to guess which fields are important. You can build on it by removing or adding fields to the dimensionality arguments of the nested LOD calcs. I removed the 'ELSE "ERROR" ' line of code. Below is the modified calculation and a screenshot of the result.  See attached workbook.

{FIXED [SKU], [Country], [Market], [Brand], [Subbrand], [Manufacturer], [Sub Category]: MIN(

{FIXED [Country], [Market], [Brand], [Manufacturer], [Subbrand], [Sub Category], [Period], [Uniqueid], [Prod Ldesc], [Prod Sdesc], [PROD Level], [Latest Year], [Latest Period]:

MIN(IF [Period] = "YTD_CY" AND [Wd (Max)] <= [Avg of X] AND [Velocity] <= [Avg of Y] THEN 'OPTIMIZED'

ELSEIF [Period] = "YTD_CY" AND [Wd (Max)] > [Avg of X] AND [Velocity] <= [Avg of Y] THEN 'LAGGARDS'

ELSEIF [Period] = "YTD_CY" AND [Wd (Max)] <= [Avg of X] AND [Velocity] > [Avg of Y] THEN 'HIDDEN GEMS'

ELSEIF [Period] = "YTD_CY" AND [Wd (Max)] > [Avg of X] AND [Velocity] > [Avg of Y] THEN 'POWER SKUs'

//ELSE "ERROR"

END)}

)}

Hope this helps.

Ossai

• ###### 9. Re: How to include particular period in IF Condition

Hi Ossai,

Amazing !!

The solution that you have provided is working perfectly!

Thank you so much

• ###### 10. Re: How to include particular period in IF Condition

You're welcome Darshan. I'm glad it helped.