Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
as you always have good ideas for solving a Problem, here is as follows:
I have data with an article number, testing date, a delivery quantity and the testing result (amount of failures)
article number | testing date | delivery quantity | amount of failures |
---|---|---|---|
505010111 | 01.01.1990 | 5 | 1 |
505010111 | 01.02.1990 | 5 | 0 |
505010112 | 01.02.1990 | 150 | 0 |
Then I created a table, that Shows the latest 3 testing results per article.
Quality Quotient: 1 - (failures / delivery quantity)
but when I use the max(date) formula the table will only return the correct value for the first line...
1 -
sum(
{< TestingDate = {"$(vPE1)"}
>}
AmountFailures)
/
sum(
{< TestingDate = {"$(vPE1)"}
>}
AmountFailures)
with vPE1 = "=max(TestingDate,1)"
If I only select one article, the calculation suddenly works...
Thank you for your help,
Kevin
May be the MaxDate for each article is different? The issue is that set analysis is evaluated once per chart. What this means is that set analysis will use the max date across all your articles instead of looking at max date for each article. If that is not what you want, you will either have to use Aggr() function or try playing around with FirstSortedValue() function.
Thank you for your Response!
The MaxDate for each article is indeed different. when used solely it works, but if used within the set Analysis, it only does return a value for the first row.
Then i will Need to look into Aggr function.
Will reply as soon as I have it up and running.
Kevin
Great... if you run into any issues, please share some sample data and/or a sample app and we are more than willing to help you out here
Share sample data.
Issue is not exact clear to me, but check once date format of TestingDate and variableDate
TestingDate = {"$(vPE1)"
Didnt know exactly how to upload a document in here, so I added the qvw and Excel to my initial question
How does this look
= num(
if(
1 - FirstSortedValue({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>} Aggr(Sum({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>}YE_WE_PosFAU.FehlerMenge), Artikel_Nr, Kalender.Datum), -Aggr(Only({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>} Kalender.Datum), Artikel_Nr, Kalender.Datum), 2)/
FirstSortedValue({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>} Aggr(Sum({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>}YE_WE_PosFAU.E_WE_LieferMenge), Artikel_Nr, Kalender.Datum), -Aggr(Only({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>} Kalender.Datum), Artikel_Nr, Kalender.Datum), 2)
< '0', '0,0',
1 - FirstSortedValue({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>} Aggr(Sum({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>}YE_WE_PosFAU.FehlerMenge), Artikel_Nr, Kalender.Datum), -Aggr(Only({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>} Kalender.Datum), Artikel_Nr, Kalender.Datum), 2)/
FirstSortedValue({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>} Aggr(Sum({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>}YE_WE_PosFAU.E_WE_LieferMenge), Artikel_Nr, Kalender.Datum), -Aggr(Only({<YE_WE_PosFAU.FehlerUrsache = {"999", ""}>} Kalender.Datum), Artikel_Nr, Kalender.Datum), 2))
, '#0,0%')
Looks very good!!!
Seems to work for the 2. testing date. What about the first testing date? Can you explain, what has to be adjusted within the formula to return the values for the first and third testing?
Is it 'just' the ",2" that has do be exchanged with ",1" or ",3"?
It is the , 2 at the end of FirstSortedValue which needs to be adjusted
Hi Kevin,
You could try this script:
Table:
LOAD YE_WE_PosFAU.E_WE_Pos_Obj,
Artikel_Nr,
Kalender.Datum,
YE_WE_PosFAU.FehlerMenge,
Fehlermenge,
FU
FROM
[Prüfschärfe 2017-06-30.xlsx]
(ooxml, embedded labels, table is Sheet1);
OrderedTable:
Load
*,
AutoNumber(Kalender.Datum,Artikel_Nr) as SeqNo
Resident Table
Order by Artikel_Nr, Kalender.Datum desc;
Drop Table Table;
Then this table:
Artikel_Nr | Exp1 | Exp2 |
---|---|---|
82.59% | 26/06/2017 | |
503010115 | 93.33% | 26/06/2017 |
503010119 | 88.89% | 14/06/2017 |
503010124 | 91.80% | 20/06/2017 |
503010195 | 26/05/2017 | |
503010197 | 66.67% | 14/06/2017 |
503010208 | 13/02/2017 | |
503010210 | 91.67% | 04/04/2017 |
503010211 | 85.71% | 06/03/2017 |
I selected the first few rows for brevity.
Exp1 : 1-Sum({$<SeqNo = {"<=3"}>}Fehlermenge)/sum({$<SeqNo = {"<=3"}>}YE_WE_PosFAU.FehlerMenge)
Exp2 : Max({$<SeqNo = {"<=3"}>}Kalender.Datum)
Regards
Andrew