8 Replies Latest reply: Apr 15, 2014 4:27 AM by Atsushi Saijo Branched to a new discussion.

# The Maximum Date in Month

Dear Gurus,

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

The source table is as such:

ProductSerialDoc#ActTypeDocDateWeight
S0001001Issued26.02.20141
S0001002Cancelled14.03.2014-1
S0001003Issued18.03.20141
S0001004Cancelled03.04.2014-1
S0001005Issued03.04.20141

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.

ProductSerialDocDateWeight
S000118.03.20141

• [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.

Atsushi

• ###### Re: The Maximum Date in Month

Atsushi,

For test purposes i prepared few more docs.

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

regards

Darek

• ###### Re: The Maximum Date in Month

Look for comm_maxdateinmonth_thr_505527.qvw

It works

• ###### Re: The Maximum Date in Month

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.

• ###### Re: The Maximum Date in Month

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

• ###### Re: Re: The Maximum Date in Month

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

• ###### Re: The Maximum Date in Month

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.

• ###### Re: The Maximum Date in Month

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

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

• ###### Re: The Maximum Date in Month

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