topic Re: Max Date in table returns only values for the first row in QlikView

Max Date in table returns only values for the first row

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Max Date in table returns only values for the first row

sunny_talwar — Thu, 29 Jun 2017 16:35:47 GMT

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.

Re: Max Date in table returns only values for the first row

Anonymous — Fri, 30 Jun 2017 06:53:56 GMT

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

Re: Max Date in table returns only values for the first row

sunny_talwar — Fri, 30 Jun 2017 11:20:48 GMT

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

Re: Max Date in table returns only values for the first row

tyagishaila — Fri, 30 Jun 2017 11:53:24 GMT

Share sample data.

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

TestingDate = {"$(vPE1)"

Re: Max Date in table returns only values for the first row

Anonymous — Fri, 30 Jun 2017 14:01:52 GMT

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

Re: Max Date in table returns only values for the first row

sunny_talwar — Fri, 30 Jun 2017 14:16:26 GMT

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',

, '#0,0%')

Re: Max Date in table returns only values for the first row

Anonymous — Fri, 30 Jun 2017 14:50:51 GMT

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

Re: Max Date in table returns only values for the first row

sunny_talwar — Fri, 30 Jun 2017 14:53:40 GMT

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

Re: Max Date in table returns only values for the first row

effinty2112 — Fri, 30 Jun 2017 15:17:00 GMT

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,

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

Re: Max Date in table returns only values for the first row

Anonymous — Mon, 03 Jul 2017 06:47:19 GMT

I will look into both Solutions today, as I am back in the Office.

Thank you so much for your help!

Kevin