How to calculate relative half period and show this percentage in key value

[uttamkumar7361]

Hi Team,

Does anyone have calculated relative half period and show this difference percentage in key value from previous half period.

since it is not directly half period calculation in sas.

let' suppose the variable is billed amount

Thanks in advance.

Regards,

Uttam Kumar

[Sam_SAS]

Hello,

What period do you want to calculate half of? Month, Year, Quarter? I think that this should probably be possible but the expression might be a bit messy-looking.

Sam

[uttamkumar7361]

I want to calculate on half yearly basis and show in percentage in key value while user select half yearly ie. H1 or H2 and after selection of year

[Sam_SAS]

I was able to get a half year calculation working:

The expression is messy as I feared:

IF ('Quarter'n =1 OR 'Quarter'n = 3)
   RETURN (RelativePeriod(_Sum_, RCOST, IgnoreAllTimeFrameFilters, 'Date (1)'n, ByQuarter, 1, Full) + Period(_Sum_, RCOST, IgnoreAllTimeFrameFilters, 'Date (1)'n, ByQuarter)) / (RelativePeriod(_Sum_, RCOST, IgnoreAllTimeFrameFilters, 'Date (1)'n, ByQuarter, -2, Full) + RelativePeriod(_Sum_, RCOST, IgnoreAllTimeFrameFilters, 'Date (1)'n, ByQuarter, -1, Full))
ELSE (RelativePeriod(_Sum_, RCOST, IgnoreAllTimeFrameFilters, 'Date (1)'n, ByQuarter, -1, Full) + Period(_Sum_, RCOST, IgnoreAllTimeFrameFilters, 'Date (1)'n, ByQuarter)) / (RelativePeriod(_Sum_, RCOST, IgnoreAllTimeFrameFilters, 'Date (1)'n, ByQuarter, -3, Full) + RelativePeriod(_Sum_, RCOST, IgnoreAllTimeFrameFilters, 'Date (1)'n, ByQuarter, -2, Full))

The basic idea:

For odd-numbered quarters, compare the current quarter (Period()) and the one after it (RelativePeriod(1)) to the preceding period (RelativePeriod(-1)) and the one before that (RelativePeriod(-2)).

For even quarters, compare the current quarter (Period()) and the one before it (RelativePeriod(-1)) to two periods ago (RelativePeriod(-2)) and the one before that (RelativePeriod(-3)).

A roadblock I hit was trying to calculate the quarter as part of this expression. Currently this is not supported, so I am pre-calculating the quarter using the Quarter() function.

I'm not sure if this actually helps  you with your goal of showing this in a Key Value, however! I think that an expression with periodic functions will always be Missing in a key value object because it doesn't have enough context to calculate the aggregations.