Dimension based on another dimension

[luizcdepaula]

Hello experts,

I am having a challenge trying to figure this out for a dimension value. I am working on a inventory report, where we need to list the primary supplier for each material. Some materials might have 2 or 3 different suppliers, so they just want the supplier that sold the most in the year to be displayed. I was able to achieve that by using FirstSortedValue function, based on total quantity purchased for the year.

The problem I am facing now, is that some materials are not used as often, and they are purchased every 2 or 3 months. So, as I am pulling the suppliers based the total quantity purchased on the year, in January and February I am getting a lot of nulls. So, they asked I can use the previous year primary supplier if the current year primary supplier is NULL.

So, I need to create a statement, with a condition that says, if primary supplier (which is defaulted for current year) is null, give me primary supplier from last year. This could easily be accomplished with set analysis if it was a measure, but I have never done it for a dimension.

Find an example below

How it is now                                                                    

How it is supposed to be

Thank you in advance,

LD

[sunny_talwar]

What is your expression? right now? Assuming is it Count([Primary Supplier]), try this:

If(Len(Trim(Count([Primary Supplier]))) > 0, Count([Primary Supplier]), Count(TOTAL <Material, Supplier>[Primary Supplier]))

[luizcdepaula]

Hi Sunny,

My expression is for a dimension, not a measure. The Primary Supplier number is not the count of suppliers, but their IDs. So, for instance, in my example, 4 is the Supplier number 4.

To achieve Primary Supplier, this is what I use

FirstSortedValue([Vendor ID],-TotalQty) as [Primary Supplier];

Group by Material,Year;

The expression I am using to get last year Primary Supplier if current year is NULL is below, but it is not working.

if(isnull([Primary Supplier]),(if(Year=year(today())-1,{Primary Supplier])),[Primary Supplier])

[sunny_talwar]

How about this:

Aggr(If(IsNull([Primary Supplier]), Above([Primary Supplier]), [Primary Supplier]), Supplier, Year)

[luizcdepaula]

The statement above did not work.

Is there a way to impose a condition, like in a set analysis, to give [Primary Supplier] for Year 2016?

For example, for a measure I would simply use Count({<Year=year(today())-1>}[Primary Vendor]). Is there a way to make a dimension (Primary Supplier) dependent on another dimension (Year)?

[sunny_talwar]

I don't think so because Year is one of the dimension in your chart. Either you have to use Above or you can create an AsOfTable -> The As-Of Table

[Anonymous]

=aggr(if(IsNull([Primary Suplier]), aggr(nodistinct FirstSortedValue({<[Primary Suplier]={"*"}>} [Primary Suplier], -Year), Material,Suplier), [Primary Suplier]), Material,Suplier,Year)

[Anonymous]

Hi Luiz, does my solution solve your problem or rather did you understand the approach?

[veeranj · Accepted solution]

Hi,

Try this .

SALES:

LOAD * INLINE [

    year, material, supplier, qty

    2016, AAA, 1, 10

    2016, AAA, 2, 8

    2016, BB, 3, 7

    2016, BB, 4, 15

    2017, AAA, 1, 15

    2017, AAA, 2, 10

    2017, BB, 3

    2017, BB, 4

];

MAXQTYSUPPLIER:

LEFT JOIN

LOAD year,material,MAX(qty) AS qty,'MAX' AS FILTER Resident SALES  GROUP BY year,material;

left join

LOAD year,material,supplier as maxsupplier Resident SALES where FILTER='MAX' ;

mapping :

mapping load year&material,maxsupplier Resident SALES ;

load * ,if(isnull(applymap('mapping',year&material,null())),applymap('mapping',year-1&material,null()),applymap('mapping',year&material,null())) as Supplier1 Resident SALES ;

take this in table box and omit rows where supplier1 is null .

Thanks,

Anjee

[luizcdepaula]

Hi Robin, thanks for the effort. It did not work, as it was not giving me top supplier for previous year. Veeranjaneyulu reply below, worked. I just had to adjust to my data model and it worked perfectly.

Thanks again!

LD