topic Dimension based on another dimension in QlikView

Dimension based on another dimension

luizcdepaula — Mon, 06 Feb 2017 17:31:20 GMT

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,

Re: Dimension based on another dimension

sunny_talwar — Mon, 06 Feb 2017 17:42:22 GMT

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]))

Re: Dimension based on another dimension

luizcdepaula — Mon, 06 Feb 2017 19:05:06 GMT

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])

Re: Dimension based on another dimension

sunny_talwar — Mon, 06 Feb 2017 19:10:27 GMT

How about this:

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

Re: Dimension based on another dimension

luizcdepaula — Mon, 06 Feb 2017 19:35:05 GMT

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)?

Re: Dimension based on another dimension

sunny_talwar — Mon, 06 Feb 2017 19:41:40 GMT

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

Re: Dimension based on another dimension

Anonymous — Mon, 06 Feb 2017 20:31:18 GMT

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

Re: Dimension based on another dimension

Anonymous — Wed, 08 Feb 2017 05:48:30 GMT

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

Re: Dimension based on another dimension

veeranj — Wed, 08 Feb 2017 06:32:08 GMT

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

Re: Dimension based on another dimension

luizcdepaula — Wed, 08 Feb 2017 15:01:09 GMT

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!

Re: Dimension based on another dimension

luizcdepaula — Wed, 08 Feb 2017 15:06:23 GMT

Hi Veeranjaneyulu.

I was able to fix it, or at least make it work with a similar solution, by creating another table, with previous year top suppliers, and then changing the year to current year, and calling it Primary_Supplier_PY.

However, I do like your approach, using the the applymap function, as it works for previous years too, without having to create different tables for each year.

Great work!

Thanks,

Re: Dimension based on another dimension

Anonymous — Wed, 08 Feb 2017 18:17:57 GMT

basically this should work, but I think it is not worth trying any further, because you have a working solution...