topic Re: set analysis for calculating amount in QlikView

set analysis for calculating amount

Wed, 28 May 2014 20:07:45 GMT

Hi,

I got to calculate the sum of all invoices for all customers whose first invoices fall in a particular month.

I am trying this expression

For example:

My data looks like this

1. Date: 3/25/2012

Customer: Customer1

Amount: 25

2. Date: 3/27/2012

Customer: Customer1

Amount: 25

3. Date: 3/28/2012

Customer: Customer2

Amount:50

The output I need is sum of amount for 1 and 3 i.e. 75 because these are the first invoices for Customer1 and Customer2.

I am using this expression

sum(if (AGGR(if(MonthName(Min({1<TxnType={'Invoice'}>}TxnDate)) = MonthName(Max(TxnDate)), 1, 0),CustomerListID, TxnLineID) > 0, if(AccountType='Income', Amount, 0)))

and it is giving me output 100.

What is going on here?

Regards,

Saurabh

Re: set analysis for calculating amount

eduardo_sommer — Wed, 28 May 2014 20:14:42 GMT

The expression is:

=sum(Aggr(FirstSortedValue(Value, DateInv),Customer))

Eduardo

Re: set analysis for calculating amount

Anonymous — Wed, 28 May 2014 20:30:13 GMT

Not sure how that expression works ....but i have an idea for your problem..which i did similarly when i had this problem:

Originaltable:

Load InvNo&CustomerNo&DateofTransaction as key

InvNo,

CustomerNo,

DateofTransaction,

Amount from A;

left join (Originaltable)

Load InvNo&CustomerNo&DateofTransaction as key,

flagmindate

;

Load

InvNo,

CustomerNo,

Date(min(DateofTransaction)) as DateofTransaction,

1 as flagmindate

resident Originaltable

group by

InvNo,

CustomerNo

;

hope this helps....

Re: set analysis for calculating amount

Anonymous — Thu, 29 May 2014 00:28:54 GMT

Hello, Ramya.

I think the Vinay's idea of creating a flag for the first invoice per costumer is a good one. But if you want to get the frist invoice per costumer per month, some changes to the code are necessary. Like this:

Invoices:
Load
  *,
  Month(Date) as Month
Inline [
    Invoice, Costumer, Date, Amount
    1, Customer1, '03/25/2012', 25
    2, Customer1, '03/27/2012', 25
    3, Customer2, '03/28/2012', 50
];

Left Join (Invoices) Load
  Costumer,
  Month,
  Date(Min(Date)) as Date,
  1 as FlagFirstInvoiceOfMonth
Resident Invoices
Group by Costumer, Month;

Then you would use this simple expression on your graphics and tables:

Sum({$<FlagFirstInvoiceOfMonth = {1}>} Amount)

An example QVW is attached. Hope it helps.

Cheers.