Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
AnnaEKinch
Partner - Contributor III
Partner - Contributor III

Set Analysis- Sum of values based on several fields

Hi!

I want a column in a table only  showing sum of costs after an employee has terminated his/her employment.

My three fields:

[Costs]

[Period] ( formatted YYYYMM)

[Period Last Day of Employment] (formatted YYYYMM)

So I want to show just one total sum for sum[Costs] for all costs where [Period]>[Period Last Day of Employment] ie excluding all costs in periods before the employment terminated.

Grateful for help 🙂 /Anna

2 Solutions

Accepted Solutions
gkhetan
Contributor II
Contributor II

Hi Anna,

You can try using an expression as below

SUM(IF([Period]>[Period Last Day of Employment], Costs))

Thanks,

GKhetan

View solution in original post

manoranjan_d
Specialist
Specialist

to do this in  set analysis , you need to create flag in the script example as below

if(date(date#(Period,'YYYYMM'),'YYYYMM')>date(date#([Period Last Day of Employment],'YYYYMM'),'YYYYMM'),'Y','N') as Flag

Text box expression

=sum({<Flag={'Y'}>}Sales)

 

 

 

 

View solution in original post

5 Replies
gkhetan
Contributor II
Contributor II

Hi Anna,

You can try using an expression as below

SUM(IF([Period]>[Period Last Day of Employment], Costs))

Thanks,

GKhetan

AnnaEKinch
Partner - Contributor III
Partner - Contributor III
Author

Thanks, I tried that, but I get all periods both before and after 😞

AnnaEKinch_0-1613323897378.png

 

AnnaEKinch
Partner - Contributor III
Partner - Contributor III
Author

I changed the Period formatting and it seems to work now! Thanks 🙂

manoranjan_d
Specialist
Specialist

to do this in  set analysis , you need to create flag in the script example as below

if(date(date#(Period,'YYYYMM'),'YYYYMM')>date(date#([Period Last Day of Employment],'YYYYMM'),'YYYYMM'),'Y','N') as Flag

Text box expression

=sum({<Flag={'Y'}>}Sales)

 

 

 

 

AnnaEKinch
Partner - Contributor III
Partner - Contributor III
Author

Yes! Thank you @manoranjan_d now I understand 🙂