The Maximum Date in Month

[atsushi_saijo]

Dear Gurus,

I have a case where I must select maximum date in a month.

The source table is as such:

ProductSerial	Doc#	ActType	DocDate	Weight
S0001	001	Issued	26.02.2014	1
S0001	002	Cancelled	14.03.2014	-1
S0001	003	Issued	18.03.2014	1
S0001	004	Cancelled	03.04.2014	-1
S0001	005	Issued	03.04.2014	1

ProductSerial 'S0001' has 5 associated documents: Doc# {001; 002; 003; 004; 005}.

• Sum all the Weight up to last Month-End, and if it were >0, then take the maximum DocDate with 'Issued' on the last month.

In this case, 18.03.2014 should be selected, and such table should be displayed.

ProductSerial	DocDate	Weight
S0001	18.03.2014	1

• [Dimention 1] = ProductSerial;
• [Dimention 2] = FirstSortedDate(DocDate,-DocDate) // does not work;
• [or Dimention 2] = If(FKDAT<=MonthEnd(Today(0),-1),Max(DocDate)) // does not work;
• [Expression] = Sum( If( InMonthToDate(DocDate, MonthEnd( Today(0), 0), 0), Weight));

Would you possibly advise how the last DocDate could be displayed?

**This 'maximum date' changes as user selects the targeted reporting month. (In this case, MonthEnd(Today(), -$(X)) ) is used with X as input value.

I'd apprecaite for your feedback.

Atsushi

[#2]

Atsushi,

i prepared example, which i hope adressess your needs

For test purposes i prepared few more docs.

Let me know if it is ok and if you like it.

regards

Darek

[#3]

Look for comm_maxdateinmonth_thr_505527.qvw

It works

[#4]

why not:

weight sum to the end of closed month:

if(sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)>0,sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight))

last issue date to the end of closed month:

date(if(sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)>0,FirstSortedValue({<ActType={'Issued'},DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}DocDate,-DocDate)))

?

regards

Darek

where X i value selected by user from field (1,2,3,4,5,....) - it is offset of months.

[#5]

Hi,

i should be back in about 2 hours...

I try to understand what's changed. I have also to recall my solution for myself It was few days ago i prepared it. From that time i prepared many solutions on this forum So, i can't remember every one.

If you need my help. please provide some test data with current and expected result. It will be fastet way for me to understand what to change

regards

Darek

[#6]

You need this?:

Lokk for attachment. There are 2 new expressions (named new1 and new2).

new1:

chooses product_serials, for which Weight in "selected" month is positive.

{<DocDate={">=$(=MonthStart(Today(0),$(=only(X))))<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)>0

for those serials calculates sum(Weght) from the beginning of that serial history ubtil end of this "selected" month

sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)

new2:

chooses product_serials, for which Weight in "selected" month is positive.

{<DocDate={">=$(=MonthStart(Today(0),$(=only(X))))<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)>0

for those serials calculates sum(Weght) from the beginning of that serial history ubtil end of this "selected" month

sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)

new3 - new criteria to choose possible records to get last issue date:

(the same condiotions as in new1 and new2).

Let me know if you need more help and if you found correct answer. If yes, you should close thread, to to avoid that people who trying to help will still working on solution for your problem....

regards

Darek

[atsushi_saijo]

Dear Darek, I deeply appreciate for your close support, and we are very close to resoluting it.

Having deliberating on it, the issue seems my side: here is the logic construction:

//Select only those Product Serial, which has any document date in the targeted month

//and then, sum up the weight, only up to the target month.

*For example, please look ProductSerial 24551. Initial document is issued on 81.02.2014, and cancelled on 12.03.2014, and re-issued within same date.

1. Let's select target month '-1', i.e. March. (Let's see ProductSerial 24551 specifically)
2. In this case, the logic must include 24551 because it has DocDate in March
3. The logic must sum up only up to MonthEnd() = end of march
4. sum +1 -1 +1, resulting in +1.

The total must be 818  in the attached QV. If it resulted as 814, it is due to some product serials having +/-1 within the same month, and offseting completely.

(previous month) +1

(this month)     -1

(this monty)     +1

Current formula summarize the this-month only, and treat as 0.

**attached is the QV file.

[atsushi_saijo]

Dear Manish, Darek, and Toni, I appreciate for your fast responses.

I have created another thread "Max(TOTAL <>) statement within If statement" based on aggragation, but this thread is valid too. I'd await for your feedback if any. In any case all the input are helpful answers.

****@18:38

I have created an AUTONUMBER(MONTH(DocDate)) as FKM#in the loading script:

	Sum(if(
	FKDAT_c<=MonthEnd(Today(0),-$(X)),
	CEIL(AGGR(Weight,ProductSerial,FKM#,ACTTYPE),1)

    ))

    -Sum(if(

	FKDAT_c<=MonthEnd(Today(0),-($(X)+1)),
	CEIL(AGGR(Weight,ProductSerial,FKM#,ACTTYPE),1)

    ))

This produced 818.

****@09:56

Darek has produced following statement, which produced the right result. Further I'd clarify the if(count() ) statement.

if(count({<DocDate={">=$(=MonthStart(Today(0),$(=-Y)))<=$(=MonthEnd(Today(0),$(=-Y)))"}>} DocDate)>0 ,sum(Weight) )

[atsushi_saijo]

Dear Manish and Toni,

I appreciate for your input. I branched out the discussion, and could not designate the helpful answer. But all in all, I appreciated your input. The very issue were resolved in two ways: [a.] one way by subtraction and change of weight assignment: [b.] another is to use if (count () ) statement.

Very best wishes

Atsushi