Set Analysis: Sum with multiple conditions

[#1]

Hi best QV Community,

I’ve been using QlikView now for few months very smoothly without any major problem until now.

I’ve been spending a lot of time recently searching for an equivalent to (Excel) SUMIFS in QlikView using the values of a dimension as input conditions. I thought this was a straight forward task but my limited knowledge proved that I was wrong.

To illustrate the problem with a simple example:

Let’s say that we have a data set containing the following fields [Date], [Employee], [Expenditures] and [Balance] (see attached picture).  Using those fields I need to calculate the daily income for each active employee. This is a straight forward task in Excel using SUMIFS or SUMPRODUCT (See the solution in Excel in the attached picture).

So in short I need to find a way to translate: D10-SUMIFS(D:D,A:A,A10-1,B:B,B10)+C10

To something like that in QlikView:  Balance - Sum ( { <Date =Date-1, Employee= Employee> } Balance) + Expenditures

I order to create a new dimension called [Income]. I also tried to use a different kind of Sum(if(conditions)...., Balance) without any success.

I belive that there must be a way to achieve that in QlikView but my knowledge in that matter seems to be limited. I therefore deeply appreciate any help from the community.

Many thanks

[#2]

Many Thanks santharubban!

This part of the solution: RangeSum (Above(Sum(Balance),Date-1,Employee))

returns only Zeros.

[MK_QSL · Accepted solution]

Please check ....

[#4]

Well Luis, I defined the variables in the script without any problem but QV didn't like the syntax. Please see attachements.

Many Yhanks.

[MK_QSL]

Please check my solution enclosed

Income1 = Income as per your excel file. Here I have count Balance of Previous Date if it is exactly one day before.

Income2 = Income if you want to count Balance for previous date also...Here I have count Balance of Previous Date even though the date is not one day before.

[#6]

please see attachement and let me know if works

Regards

[#7]

Bullseye Manish! It worked like a dreem!

But if I may ask. Is there any simular solution using an expression?

1000 X Thanks again for the solution.

I'll even try all possible solutions you may send and let you know if it works.

[MK_QSL]

Create a straight table

Dimensions :

1) Date

2) Employee

Expressions:
1) SUM(Expenditure)

2) SUM(Balance)

3)

     If(Employee = Above(Employee) and NUM(Date) = NUM(Above(Date))+1,

         Balance+Expenditure-Above(Balance),

         Balance+Expenditure)

Sort the table by Employee, Date

Hope this help...

[MK_QSL]

Please check enclosed file...

There are three possible results..

1) Straight Table (UI end)

2) Pivot Table     (UI end)

3) Script           (Script end)

[#10]

Excellent answer Luis. It works perfectly! Exactly what I was looking for.

Many many Thanks!

[#11]

you welcome CK1000qv

Best regards and have a nice day

Luis