Display 4 weeks data in pivot table in Qlik Sense

[LP27]

Hi Everyone,

I have got the below case of displaying  only 4 weeks of data in Pivot table.

This is my pivot table-

Emp No	Week 51	Week - 50	Week - 49	Week - 48	Week - 47
001	-	2	4	-	1
003	-	4	8	-	8
004	-	8	-	-	7
002	-	-	2	7	2

So now i want to display only last two weeks, Since today is Week-50, I need to display only column Week - 50 and Week-49.

And i am using sum(salary) as Measure. I also need to display only those salary that are Dashes in last 2 weeks . 

Like the below -

Emp No	Week - 50	Week - 49
004	8	-
002	-	2

 Please let me know if you have question.

Thanks in Advance.

[sunny_talwar]

You can give this a shot

Sum({<[Emp] = {"=Count(DISTINCT {<[Calendar day.autoCalendar.WeeksAgo] = {[>=$(=Min([Calendar day.autoCalendar.WeeksAgo]))<=$(=Min([Calendar day.autoCalendar.WeeksAgo])+3)]}>} If(Em_num = Emp, [Calendar day.autoCalendar.WeeksAgo])) = 1"},
	  [Calendar day.autoCalendar.WeeksAgo] = {">=$(=Min([Calendar day.autoCalendar.WeeksAgo]))<=$(=Min([Calendar day.autoCalendar.WeeksAgo])+3)"}>} [Actual Time])

[sunny_talwar]

Something is wrong with the attached file Actual Time (1).qvf. It seems to have been corrupt. Would you be able to reload and attach again.

[LP27]

I have re-attached the file.

I have now connected two tables with common filed. 

I need latest 4 week Column only. 

Note:

In the Employee Id column i need the list employees who's names are present in both tables only! ( i have done that in dimension)

[sunny_talwar · Accepted solution]

I am not sure why it isn't working for me.. but can you try this expression

Sum({<[Emp] = {"=Count(DISTINCT {<[Calendar day.autoCalendar.WeeksAgo] = {[>=$(=Min([Calendar day.autoCalendar.WeeksAgo]))<=$(=Min([Calendar day.autoCalendar.WeeksAgo])+3)]}>} If(Em_num = Emp, [Calendar day.autoCalendar.WeeksAgo])) = 1"},
	  [Calendar day.autoCalendar.WeeksAgo] = {">=$(=Min([Calendar day.autoCalendar.WeeksAgo]))<=$(=Min([Calendar day.autoCalendar.WeeksAgo])+3)"}>} [Actual Time])

instead of

sum([Actual Time])

and change the dimension

=if([Calendar day.autoCalendar.Week] >= Week(today(),-4),[Calendar day.autoCalendar.Week])

to

[Calendar day.autoCalendar.Week]

and see if it works?

[LP27]

This worked Sunny! 

Really Appreciate your help!

Thanks 🙂

[sunny_talwar]

Awesome!!