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

[Anonymous]

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.

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

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.

[MK9885]

Can you please upload sample data?

[MK9885]

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]

‌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]

Can you please provide a sample data in xl?

[Anonymous]

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]

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]

This Excel format will not work to achieve required results.

[Mark_Little]

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