Min in Dimension

[reporting_neu]

Hello,

how can I use "Min" in a dimension in this case?

if(S_PartPrice.Diff = Min(S_PartPrice.Diff), S_Part_Pricelist.Part, null())

I tried with AGGR. But it didn't work.

if(S_PartPrice.Diff = Min(AGGR(S_PartPrice.Diff, S_PartPrice.Part)), S_Part_Pricelist.Part, null())

[matthias_v]

Hi,

Try the following:

Aggr(if(S_PartPrice.Diff = Min(S_PartPrice.Diff), S_Part_Pricelist.Part, null()), S_Part_Pricelist.Part)

[Digvijay_Singh]

Are you trying this in the script or in the chart expression? Pl share the full load statement, what do you mean by not working? Is it throwing some error or showing unexpected output?

[reporting_neu]

Hi and thank you.

It is an expression. I'd like to exclude some values, so I need to create a formula of some sort.

The table looks like:

part	valid from	valid to	price
100	01.01.2022	31.12.2022	5,00
100	15.02.2022	31.12.2022	4,00
100	01.03.2022	31.12.2022	4,50

The user enters a "valid on" date via a variable. The formula checks which price is valid on the "valid on" date and only outputs this result in the table. That's the wish.

Example: I enter the date 26.02.2022. The second line with the price 4,00 is then output.

The formula should check whether the "Valid from" field is <= to the entered variable "Valid on". Because, as in this case, "01/01/2022" would also be smaller, two results would be displayed. I therefore calculated the difference between the "valid from" date and today in the load script ("diff" field). It should also take the smallest "diff" value in the formula.

So the original formula looks like this:

if(('$(vValidOn)'>=S_PartPrice.Until) and ('$(vValidOn)' <= S_PartPrice.ValidTo),S_PartPrice.Part, null())

EDIT:
I'm just noticing that there's no point in calculating the difference between "valid from" and today. If I enter a date from the past, I would exclude values. Too bad. Do you have a solution how I only get the correct value displayed when entering the variable?

[reporting_neu]

Can someone help?

I basically just need a formula that is supposed to check which "valid from" date applies when I enter a variable (vValidOn) date.