Link Unit Cost to Table: Rule

[qlikviewaf]

Hi guys,

i have a table with different unit-cost based on year.

Cost Method 07 is always the current year, then every year a new Cost Method is created to store the previous year Cost.

I would like to link this table to my Sales Table using the Item (really easy) and then the Year, in order to evaluate sales based on the real cost for that specific year. I'm thinking to use the first 4 digit of the cost-method to retrieve the year that will create the link with my Master Calendar Table. But what about the Cost Method 07 which is the current year? How can i create a rule to say, 07 is always current year (.e.g 2017) ?

Thank you!

[ahaahaaha]

Hi,

If I understood you correctly.

As variant (look attached file), at the script level

If(IsNull(Sign(Num(Left(CostMethodDescription, 4))))=0, Left(CostMethodDescription, 4), '2017'),

in chart

If(RangeSum(Left(CostMethodDescription, 4))>0, Left(CostMethodDescription, 4), 2017)

Meaning of calculations. If there are four left digits, then this is the value of the year. If not numbers, then define the value as 2017.

Regards,

Andrey