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

[MK_QSL]

Can you provide some sample data or apps?

This can be done by using previous and peek function in script.

[#3]

Hi, you  can created 2 variables:

vDate= date-1

vEmployee= employee

so your expression changes to this way

Balance - Sum({Date={'$(vDate)'}, Employee={'$(vEmployee)'}} Balance) + Expenditures

if this does not help you, please attach an qvw example

regards,

Luis

[santharubban]

try this

(balance) - (RangeSum(Above(Sum(balance),Date-1,Employee))+sum(Expenditures))

else upload the sample application

[#5]

Hi Manish,

Thanks for you reply. Well the actual data set is huge. But I created a similar invirement to simplify. Please see the attached Excel Data set. You can just load Date, Employee, Expenditures and Balance and help me to calculate the Income as it is mentioned in the Excel solution.

Many thanks!

[ashwanin]

Your Income is the sum of Balance + Expenditure. Put below in the expression for the output.

Sum({Date={'$(vDate)'}, Employee={'$(vEmployee)'}} Balance) + Expenditures

[MK_QSL]

Please check enclosed file and let me know if anything wrong.

UPDATE.... please change script as below...

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

    Balance+Expenditure-Previous(Balance),

         Balance+Expenditure) as Income,

[MK_QSL]

I have attached updated/corrected file.

Please check and let me know.

[#9]

Hi Luis,

And many thanks for your reply. I'll try this and let you know if it works.

Many Thanks.

[#10]

Thanks, I need to consider the balance from the previous date as an income too...