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: 
Anonymous
Not applicable

Sum of last 6 months salary where months are in columns in source

Hi ,

I have a excel file where I have months as headers and salaries of employees for 2017 as  sample data shown in the screenshot.source_table.png

I have to get output as last six months salary of employees for 2017 as shown below

output.png

Each month we new month salaries updated so we have to get sum of last six months salary and this continues every month.

Can anyone help me on this.

Thanks in advance.

8 Replies
MK9885
Master II
Master II

Can you please upload sample data?

MK9885
Master II
Master II

If( YOURDATEFIELDHERE> monthstart(addmonths(today(),-5)) and YOURDATEFIELDHERE<= today(),1,0) as [Rolling 6]

Use the above expression in your script. this would give you a flag.

In frontend you can use

sum({<[Rolling 6]={1}>}Employee_ID)

This would give sum of last 6 months from todays date.

Anonymous
Not applicable
Author

‌Hi Aehman,

Thanks for the response . I don't have date field here. I just have column names with months. So in this case what should I give in the expression above in the place of date field?

Thanks in advance

MK9885
Master II
Master II

Can you please provide a sample data in xl?

Anonymous
Not applicable
Author

Hi Aehman,

I attached sample data in excel from this file I want only last six months data in qlikview and sum of last six months salary.

Thanks in advance.

MK9885
Master II
Master II

I had to modify your XL Data into other format.

As it was not exactly the Months... it was just a text and qlik wasn't reading it.

Check for Sheet 2 in attached xl file.

Plus I did few preceding load to get the results. Not the ideal way to do it. I'm sure there must be a better way.

But I did what I could.

PFA.

Note: Even if the Months are in Column it is do able but your XL wasn't as in Months format.

But that should give you an idea.

Thanks.

Anonymous
Not applicable
Author

This Excel format will not work to achieve required results.

Mark_Little
Luminary
Luminary

Hi Vijay,

I would look at the below link for the Cross table function in script.

Crosstable ‒ QlikView

This will format your data in a better way to achieve what you would like to do. Then I would normally flag any date range I am interested in my load script to make my set analysis easier. Like this

IF(TempDate <= MONTHSTART(TODAY(1)) AND TempDate >= ADDMONTHS(MONTHSTART(TODAY(1)),-6),1) AS F_ROLLING_6_MONTHS

Then you Ste Analysis would be something like,

SUM({<F_ROLLING_6_MONTHS={1}>}salaries)

Hope this helps.

Mark