topic Re: Max(TOTAL <>) statement within If statement in QlikView

Max(TOTAL <>) statement within If statement

atsushi_saijo — Mon, 14 Apr 2014 13:39:23 GMT

Dear Sirs,

*Attached is the QV file in the very issue:

Below expression does not work.

Sum(if(DocDate<=MonthEnd(Today(0),-$(Y))

and MonthName(Date(Max(TOTAL <ProductSerial>

if( Date(DocDate)<=MonthEnd(Today(0),-$(Y)),DocDate) )))=Monthname(MonthEnd(Today(0),-$(Y)))

,Weight))

However, simple expression works as such:

Sum(if(DocDate<=MonthEnd(Today(0),-$(Y))

,Weight))

Date(Max(TOTAL <ProductSerial>

if( Date(DocDate)<=MonthEnd(Today(0),-$(Y)),DocDate) ))

The requiement of the expression is:

SumWeight only if the maximum DocDate is in reporting range;
Reporting Range is defined as <=MonthEnd(Today(0), -$(Y)), where Y is integer, which user input.

[Example] Please see below example: where 3 DocDate exists.

Current reporting month is March. (User selected 1)

Here, the maximum DocDate is 12.03.2014. This document is also in the reporting month: i.e.

InMonthToDate(

Date(Max(TOTAL <ProductSerial>

if( Date(DocDate)<=MonthEnd(Today(0),-$(Y)),DocDate) )), MonthEnd(Today(0), -$(Y)) ) = -1

This must result in 1.

On the contrary, there would be such case (maximum date is not in the March, but either future date such as April, or older date.)

In such case, the Expression must exclude counting the Weight. Althoguh total is 1, this must result in 0 since maximum DocDate is not in March 2014.

Would you possibly advise how we could implement this? I'd appreciate for your wisdom.

*The result of this report must produce 818 instead of 814. If 814, it means Expression has calculated only on the DocDate in current month.

*Specifically, this is the stuation where SAP VBRK/P (SD) table making sum per product serial. DocDate is FKDAT. Document Action Type is either billing cancellation or invoice issue.

Re: Max(TOTAL <>) statement within If statement

Mon, 14 Apr 2014 15:03:08 GMT

Hi Atsushi,

Put simply, you need to get the sum of all Weights but only for these SerialNubers, whose maximum date is not greater than the end of the month specified by the user?

regards

Darek

Re: Max(TOTAL <>) statement within If statement

atsushi_saijo — Mon, 14 Apr 2014 15:12:28 GMT

Dear Darek, I deeply appreciate for your support.

Yes, you are right. The key is:

The only ProductSerial, which has any DocDate (regardless of if it is maximum or minimum....) we must sum the Weight.For the rest of the entries, which does not have DocDate within the month, we must exclude it from summing.

Please assume that user aims Reporting Month as March (shift -1) in the situation like below:

ProductSerial; DocDate; Weight

000001; 01.02.2014; Issue; +1

000001; 01.03.2014; Cancel; -1

000001; 04.03.2014; Issue; +1

Then total is +1 (summing its total history in the source system).

But if this is such, we must exclude from the summation.

ProductSerial; DocDate; Weight

000002; 01.02.2014; Issue; +1

000002; 10.04.2014; Cancel; -1

000002; 11.04.2014; Issue; +1

So this will calculate Monthly Whole-sales figure. Why we cannot just use the maximum date of issue date? It is for back-dateability. If user has issued the invoice, but if it were cancelled in another month, user wishes to see the issued invoice as +1 even if it were reversed in later time.

I'd appreciate for your feedback in advance.

Atsushi

PS. And this would be great contribution for any QV practitioners who creates reports in SAP FI or SD area based on VBRK/VBRP table extractions.

Re: Re: Max(TOTAL <>) statement within If statement

Mon, 14 Apr 2014 17:47:50 GMT

Please check results

Re: Max(TOTAL <>) statement within If statement

atsushi_saijo — Tue, 15 Apr 2014 07:51:46 GMT

Dear Darek, I appreciate for your always excellent follow-ups. Besides I appreciate your all expression-statements, which provides with new source of learning.

I have examined the new statement:

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

[Detail Examination]

if( count(

{<DocDate

={">=$(=MonthStart(Today(0),$(=-Y+1)))"}>}

DocDate)=0,

//count for DocDate for those DocDate >= reporting month, and if it were zero,

sum(Weight) )

//sum up all weight for above

So if we change the condition statement COUNT(....) >0, 819 is produced!

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

For Monthly Wholesales purpose, COUNT condition could be further changed as:

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

This resulted in 818, identical to what source system produced. I'd like to designate yours as correct answer and I'd appreciate for all help in this enquiry.

*To clarify the logic, why COUNT produces the right condition? I was thinking somehow we had to create an array of ProductSerial, with which we execute summation. But this method do not need direct selection of ProductSerial. Why does it produces the right result?

Re: Max(TOTAL <>) statement within If statement

Tue, 15 Apr 2014 08:06:04 GMT

Condition:

count({<DocDate={">=$(=MonthStart(Today(0),$(=-Y+1)))"}>}DocDate)=0.

is checking, if there is any document newer than selected month.

If it is'nt (we are in the context of serial because of the chart dimension), that means, that we can simple sum weights (they all need to be older than your monthend).

After some time fighting with some problem it may be good to "reset" yourself and try to find a way from other side. Often it is simplier

regards

Darek

Re: Max(TOTAL <>) statement within If statement

atsushi_saijo — Tue, 15 Apr 2014 08:11:11 GMT

Dear Darek, I appreciate for your support. I have designated Correct Answer. Atsushi

Re: Max(TOTAL <>) statement within If statement

Tue, 15 Apr 2014 08:16:30 GMT

I glad, that my answers was helpful

Always keep your application, data model and expressions as clear and simple as possible!

Take care,

Darek