Max Date in table returns only values for the first row

[Anonymous]

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

[sunny_talwar]

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.

[Anonymous]

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

[sunny_talwar]

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

[tyagishaila]

Share sample data.

Issue is not exact clear to me, but check once date format of TestingDate and variableDate

TestingDate  = {"$(vPE1)"

[Anonymous]

Didnt know exactly how to upload a document in here, so I added the qvw and Excel to my initial question

[sunny_talwar]

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%')

[Anonymous]

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

[sunny_talwar]

It is the , 2 at the end of FirstSortedValue which needs to be adjusted

[effinty2112]

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