calculate only unique numbers, add in column

[kseniyav]

Hi All !

I have two files and connected their by key in script

1st file - complaints

2nd file - market perfomance FMOT (%A - zone of quality)

Key in script:  (text(Завод_SAP) & text(Month ("Дата поступления претензии")& text(Year ("Дата поступления претензии")& text("Код Продукта")))) as key

But I faced with some problem. May be somebody give me an advice

Problem 1: All complaints – count of “Всего” (“Всего” – number of complaint) but this number dublicate if this product have the FMOT too

Question 1: how to calculate only unique  numbers in column “Всего” for complaints calculation?

Problem 2: Source of claim – D and C.

I  want to calculate for plant or product in the same time KPIs:

#1.Count of complaints from D

#2. Count of complaints from C

#3. FMOT result % (only Fmot results without 10-20 points) (If I want to find out FMOT cause I want to click on FMOT and  go deeper to all defect types (visualization: 10-20 points and total FMOT lines)

Now I can’t to do it. Because I can’t choose in the same time two filters Source of claim and FMOT, they are exclude each other...

Question 2: How I can solve this problem?  

May be add name “FMOT” in the column Source of claim? How I can do it?

But how to calculate according to the logic of KPI #3 ??

Thank you in advance!

[kseniyav]

I solved the problem 1 by: Count({$<[Всего]=-{'Volume'}>}Distinct [Всего])  🙂

Any idea how to solve problem 2?

[petter]

Answer 1:

As long as you have the FMOT as a dimension you will get multiple rows for each Bcero. If you remove the FMOT you will most probably get a single row per Bcero. If you need (some of) the information from FMOT you can make it as a measure but then you will have to aggregate the FMOT values into some text or number.

To do the latter you could remove FMOT and create a new FMOT measure with the expression:

     Concat( Left( FMOT , 2) , ',' , FMOT)

The Concat() function concatenates the multiple values into a single string separation character(s) of your choice.

You could simply use:

     Concat( FMOT , ',' , FMOT )   

Which will give a very long string and only the hover-over popup will display you all of or most of the string

or more sophisticated leaving out all the FMOTs that don't start with a number:  

     Concat( If( IsNum(Left( FMOT , 2)),Left(FMOT,2),Null()) , ',' , FMOT)    

This will give you a single line of text something like this:  10/11/12/13/14 ....

If you rather want to count the FMOTs you can simply have this as an expression in the measure FMOT:

     Count(FMOT)

[petter]

Could you share a test application or some sample data?

[kseniyav]

Thank you Petter for answer. I need time to understand your advise.

I forced with issue in my 1st problem. May be you have ideas?

My first formula was All complaints = Count [Всего] but I needed count only unique numbesr and without Volume

New formula in column All complaints (1) = Count({$<[Всего]=-{'Volume'}>}Distinct [Всего]) it works well in year, plant filters:

But when I click on specific defect number – it doesn’t work and table displays extra numbers. I can’t understand the reason…vety strange...