I want the Minimum value of a product amongst same months but different years

Mahdiyar

Hello everyone . I have a question that could not find an answer for it . I would appreciate if someone could find a way to solve it . thanks .

Question : I have a module that has two dimension : 1. Time dimension that has 2 years and the scale is month. 2.Product list

In this module I have a line item named Value which i get the data from another source . in the second line item I want to have the minimum amount of the value ( line item number 1 ) amongst 2 years but within the same month . For example if product A value for Jan 24 is 70 and for Jan 25 is 52 , i want to show number 52 for both Jan 24 and Jan 25 and also a text ( in another line item ) to specify that month . For better understanding this issue i will attach a picture to this post with desired value i want to be populated .I know there is another post in community like this but in my module time is not a custom list and also there are 2 years of data not just 1 . Thank you

rob_marshall

@Mahdiyar

Well, you did say you had a two year timescale so I went off the original requirements. With that said, I have you covered, but it does take a bit more steps.

Here is the final solution, but will go into detail after.

How was this done?

Summing over Native Time is very difficult because each detail member (in this case, the month) is a separate block of data. Due to this, create a Fake/Customized Month list, please make sure all of the months in your timescale are accounted for in your custom list.

Also, you will need to create another custom list for just the names of the months. I highly recommend giving a code to these month names (1-12) as it will make the mapping much easier.

Create a SYS module for the custom months list

Now, the fun stuff.

Before we get started, you will need to add these two line items to the original module. Note, Month is only dimensionalized by Time.

Create a module to get the data out of the original module to the customized Month module.

Value:'Min - V2'.Stage Value[LOOKUP: SYS Fake Months.Time list]

Month Text: IF Value = DIVIDE(1, 0) THEN BLANK ELSE IF Value = 'CALC Min V2'.Min[LOOKUP: SYS Fake Months.Month Name] THEN SYS Fake Months.Item txt ELSE BLANK

Now that we have the data at the Month level, we now need to get the data at the Month Name level.

Min: 'CALC Min V2 - Stage'.Value[MIN: SYS Fake Months.Month Name]

Month Text: 'CALC Min V2 - Stage'.Month Text[FIRSTNONBLANK: SYS Fake Months.Month Name]

Now the final product:

Desired Value: IF Desired Stage = DIVIDE(1, 0) THEN 0 ELSE Desired Stage

Desired Text: IF Desired Value > 0 THEN "This is min value for " & 'CALC Min V2'.Month Text[LOOKUP: Month] ELSE BLANK

Desired Stage: 'CALC Min V2'.Min[LOOKUP: Month]

Stage Value: IF Value = 0 THEN DIVIDE(1, 0) ELSE Value

for a final of

Special shoutout to @MarkWarren for the Divide(1,0) trick to get infinity on calcs which is larger than NaN.

Hope this helps,

Rob

rob_marshall

@Mahdiyar

Something like this?

First, setup a SYS Time Year module dimensionalized by Time at the Year level. Create a line item called Multiplier where your first year is 1 and your second year is -1.

Here is the blueprint for the main module:

Formulas:

Desired Value: IF Value = 0 THEN 0 ELSE IF Value < OFFSET(Value, 12 * Multiplier, Value) THEN Value ELSE OFFSET(Value, 12 * Multiplier, Value)

Desired Text: IF Value <> 0 THEN "This is min value for " & Month Text ELSE BLANK

Month: NAME(ITEM(Time))…..Notice this is not dimensionalized by Products i the Applies To. In fact, you should really get this from a SYS Time Month module and can do away with this line item as long as you substitute SYS Time Month.Month where I referenced Month.

Month Text: IF Value < MOVINGSUM(Value, 12 * Multiplier, 12 * Multiplier) OR Value = Desired Value THEN Month ELSE MOVINGSUM(Month, 12 * Multiplier, 12 * Multiplier)

Multiplier: YEARVALUE(SYS Time Year.Mulltiplier)….Again, not dimenisionalized by Project, only Time.

Let me know how it goes,

Rob

Mahdiyar

@rob_marshall Hi Rob . Thank you for providing such an excellent solution . Yes this is the exact answer i want . But there is one more thing i need to ask here . What would happen if the number of years is more than 2 years like 4 years? cause in that case i dont think this design would work . i would like to see your or other people comments on this . Thank you

Mahdiyar

@rob_marshall Wow . Thank you for your perfect explanation Rob. I got my answer completely