Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Conditional Sum with different dates

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

11 Replies
Gysbert_Wassenaar

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

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


talk is cheap, supply exceeds demand
Not applicable
Author

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!

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

Please post a small qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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

Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
Not applicable
Author

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