topic Exclude value(s) from dimension base on another table in QlikView

Exclude value(s) from dimension base on another table

Anonymous — Tue, 19 Dec 2017 19:46:14 GMT

Hi all,

I am a student and doing a school BI assignment and need help. Thanks in advance.

I have 2 tables: Salesperson_Master and SalesRecords_History

I want to analyse only active Sales Person from SalesRecords_History table, those highlighted in yellow.
- S4 have left
- S1 use to be responsible for C3, which now is taken care of by S2

It should not be at script level since salesperson_master table is always updated.

I will be using barchart and dimension as SalesPersonID. I will be only interested in the total salesAmount of S1, S2 and S3.

Thanks.

Re: Exclude value(s) from dimension base on another table

Anonymous — Tue, 19 Dec 2017 20:22:33 GMT

This is just a simplify view of my assignments. There are multiple tables and fields have to renamed to eliminate synthetic keys.

Re: Exclude value(s) from dimension base on another table

Sergey_Shuklin — Wed, 20 Dec 2017 06:05:53 GMT

Hello!

I didn't catch a point of this part "S1 use to be responsible for C3, which now is taken care of by S2".

And what your expected output should be? What analytics you have to do? (instead of total sales amount)

Re: Exclude value(s) from dimension base on another table

Anil_Babu_Samineni — Wed, 20 Dec 2017 06:14:19 GMT

Welcome to the community, And even your first thread you are creating that's really neat and clean to answer.

Perhaps this?

Sum(If(SalesPersonID_Master = SalesPersonID,SalesAmount))

Count(If(SalesPersonID_Master = SalesPersonID,SalesAmount))

Sum({<SalesPersonID_Master = {"=SalesPersonID_Master=SalesPersonID"}>} SalesAmount)

Re: Exclude value(s) from dimension base on another table

Anonymous — Wed, 20 Dec 2017 15:20:22 GMT

Hi,

"S1 use to be responsible for C3, which now is taken care of by S2".

It just meant to say in the past Customer3 is managed by SalesPerson1, now Customer3 is managed by SalesPerson2.

Essentially I will want to analyst the amount of sales for S1,S2 and S3. Not interested in S4 as he is no longer in the organisation.

Thks.

Re: Exclude value(s) from dimension base on another table

Anonymous — Wed, 20 Dec 2017 15:47:09 GMT

Hi, thanks for the reply. However the results is not what I am looking for.