Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
I solved the problem 1 by: Count({$<[Всего]=-{'Volume'}>}Distinct [Всего]) 🙂
Any idea how to solve problem 2?
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)
Could you share a test application or some sample data?
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...