topic Re: Conditional Sum with different dates in QlikView

Conditional Sum with different dates

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I need to show in a chart employee last year salary on a table that comes with all salary based on different dates.

Basically the table have:

employee_name

emplyee_company

employee_salary_from

employee_salary_to

employee_salary

Each employee comes with different rows each and the last one is just the one where the field employee_salary_from is filled with a date and the field employee_salary_to is not filled.

I'm unaware on how to sum only the last salary (the one where employee_salary_to is empty).

Cold someone help?

Thank you

Re: Conditional Sum with different dates

Gysbert_Wassenaar — Fri, 15 Apr 2016 14:44:02 GMT

Try sum({<employee_salary_to-={'*'}>}employee_salary)

Or try sum({1-<employee_salary_to={'*'}>}employee_salary)

Re: Conditional Sum with different dates

Fri, 15 Apr 2016 15:11:40 GMT

Dear Gysbert,

many thanks for your reply. it seems the syntax sum({1-<employee_salary_to={'*'}>}employee_salary) works as I got some results. Unfortunately just now realized that there is an other table for the company position releated to the main salary table and the sum of salary is also depending of that.

So the final result Is that I have to tables joined with the employee ID and additional two fields named employee_engagement_from and employee_engagement_to with same filling method the field employee_engagement_to is blank for the last employee position.

So finally my sum should be conditioned from blanks values of both employee_salary_to and employee_engagement_to. Any ideas on how could change the formula?

Thank you so much for your help!

Re: Conditional Sum with different dates

Gysbert_Wassenaar — Fri, 15 Apr 2016 15:27:39 GMT

Ok, try sum({1-<employee_salary_to={'*'}>-<employee_engagement_to={'*'}>}employee_salary)

Re: Conditional Sum with different dates

Fri, 15 Apr 2016 15:56:58 GMT

Thank you Gysbert much appreciated.

I'm not sure but it seem that I have same issue of the first formula. Try to explain:

1. I need to select an employee from the employee list box to see the result in the chart table and even I select just one employee I can see all the employee in the chart

2. the sum wan't work as it show a different value greater then the salary. In the fact I just need to show the salary (not need to sum different rows values) as the row will be just one but unaware if the formula may change.

Any ideas?

Thank you

Re: Conditional Sum with different dates

Gysbert_Wassenaar — Fri, 15 Apr 2016 16:04:12 GMT

Please post a small qlikview document that demonstrates the problem.

Re: Conditional Sum with different dates

Tue, 26 Apr 2016 09:32:15 GMT

Dear Gysbert,

many thanks for your help and sorry for delay in my answer. Unfortunately I was in sick.

Please find attached the qlik view sample.

Thank you for your time

Re: Conditional Sum with different dates

Wed, 27 Apr 2016 07:09:56 GMT

Dear Gysbert Wassenaar sorry to disturb you but just wondered to know if you had chance tio take a look to the QV sample. Thank you for your time,

Massimo

Re: Conditional Sum with different dates

Gysbert_Wassenaar — Wed, 27 Apr 2016 13:49:17 GMT

Yes, the easy way out is to mark the records where To_Salary is null in the script in a preceding load and create a new flag field for it.

Payroll:

LOAD *, If(Len(Trim(To_Salary))=0,1,0) as IsLastSalary;

SQL SELECT `ID_employee`,

`ID_Payroll`,

Base as `salary`,

`From` as `From_salary`,

`To` as `To_salary`

FROM Payroll

Then you can simply sum the the salary values where that flag field has the value 1: sum({<IsLastSalary={1}>}salary)

Re: Conditional Sum with different dates

Wed, 27 Apr 2016 15:48:46 GMT

DEar Gysbert,

many thanks for ypur reply.

Unfortunately the LOAD *, If(Len(Trim(To_Salary))=0,1,0) as IsLastSalary; seems not working as I got the message tat field To_Salary is not found. i also tried to change it with original name "To" but same error. I'm loading this data from a MDB access DB. What I can't undestand is the "from" clausle on this script how works on the first load *. I mean how he know where to load the data?

Thank you for your help

Re: Conditional Sum with different dates

Gysbert_Wassenaar — Wed, 27 Apr 2016 15:50:36 GMT

Ah, yes, a typo. Change it to To_salary.

The load statement is a preceding load. The source table is the record set returned by your SQL statement. See this blog post for an explanation: Preceding Load

Re: Conditional Sum with different dates

Wed, 27 Apr 2016 16:19:16 GMT

It works!!!!! Many thans Gysbert! You give me a new way to look at solutions.

Have a nice day!