topic Re: Re: Set Analysis: Sum with multiple conditions in QlikView

Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 14:25:10 GMT

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

Re: Set Analysis: Sum with multiple conditions

MK_QSL — Tue, 18 Feb 2014 14:31:21 GMT

Can you provide some sample data or apps?

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

Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 14:39:59 GMT

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

Re: Set Analysis: Sum with multiple conditions

santharubban — Tue, 18 Feb 2014 14:47:02 GMT

try this

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

else upload the sample application

Re: Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 14:47:56 GMT

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!

Re: Set Analysis: Sum with multiple conditions

ashwanin — Tue, 18 Feb 2014 14:54:06 GMT

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

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

Re: Re: Re: Set Analysis: Sum with multiple conditions

MK_QSL — Tue, 18 Feb 2014 14:57:31 GMT

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,

Re: Re: Re: Set Analysis: Sum with multiple conditions

MK_QSL — Tue, 18 Feb 2014 15:04:14 GMT

I have attached updated/corrected file.

Please check and let me know.

Re: Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 15:21:40 GMT

Hi Luis,

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

Many Thanks.

Re: Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 15:25:26 GMT

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

Re: Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 15:27:53 GMT

Many Thanks santharubban!

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

returns only Zeros.

Re: Re: Re: Re: Set Analysis: Sum with multiple conditions

MK_QSL — Tue, 18 Feb 2014 15:36:42 GMT

Please check ....

Re: Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 15:43:40 GMT

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

Many Yhanks.

Re: Re: Re: Set Analysis: Sum with multiple conditions

MK_QSL — Tue, 18 Feb 2014 15:46:50 GMT

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.

Re: Re: Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 15:52:58 GMT

please see attachement and let me know if works

Regards

Re: Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 15:57:37 GMT

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.

Re: Set Analysis: Sum with multiple conditions

MK_QSL — Tue, 18 Feb 2014 16:10:14 GMT

Create a straight table

Dimensions :

1) Date

2) Employee

Expressions:
1) SUM(Expenditure)

2) SUM(Balance)

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

Re: Re: Set Analysis: Sum with multiple conditions

MK_QSL — Tue, 18 Feb 2014 16:15:14 GMT

Please check enclosed file...

There are three possible results..

1) Straight Table (UI end)

2) Pivot Table (UI end)

3) Script (Script end)

Re: Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 16:36:55 GMT

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

Many many Thanks!

Re: Re: Set Analysis: Sum with multiple conditions

Tue, 18 Feb 2014 16:41:48 GMT

you welcome CK1000qv

Best regards and have a nice day

Luis