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.

[Taoufiq_Zarra]

Hi,

I propose the following solution:

for the example I use the data:

LOAD Date(Date,'MM/DD/YYYY') as DD,* INLINE [
    EmpNo, Date, Sales
    001, 09/12/2019, 1
    001, 03/12/2019, 2
    001, 05/12/2019, 3
    001, 25/11/2019, 4
    001, 27/11/2019, 5
    002, 09/12/2019, 1
    003, 27/11/2019, 2
    004, 05/12/2019, 3
    001, 25/11/2019, 4
    005, 09/12/2019, 5
];

then I create a table pivot with the dimension :

[EmpNo]

and the calculated dimension:

=if(DD>=Date(today() -7, 'MM/DD/YYYY'),week(DD))

and in the expression sum(salary)

I obtain the following results

(check delete if value is null)

I hope this solution will be okay for you

Cheers,

[LP27]

Thanks for the reply!

I am now able to display only last two weeks (week 50 and 49)  but how to display only '-' or dash's in the values of Week 50 and Week 49(note: measure i am using is Sum[sal]), to get something like this below-

Emp	Week 50	Week 49
002	-	1
004	3	-
005	-	5

Note : I tried isnull() and Len(trim(sum(sal)))=0  and did not get the results.

Thanks in advance! 

[Taoufiq_Zarra]

I didn't quite understand that.

for the example

Emp	Week 50	Week 49
002	-	1
004	3	-
005	-	5

what exactly do you want to keep as output?

[LP27]

I am looking to display only Dashes or '-' for the row of week 50 and 49.

So based on your logic i have got the below table as output.

Emp	Week 49	Week 50
001	5	1
002	-	1
004	3	-
005	-	5

But now i want to display only the rows that has '-' or dash.

Like the below as output-

Emp	Week 49	Week 50
002	-	1
004	3	-
005	-	5

The above table contains the value that has only  ' - ' in them.

[Anil_Babu_Samineni]

I am using @Taoufiq_Zarra  expression

=if(DD>=Date(today() -7, 'MM/DD/YYYY'), IsNull(week(DD)))

[LP27]

Hi Anil,

I am not getting the desired output based on your expression. 

This is what i am getting.

EmpNo	if(DD>=Date(today()-7, 'MM/DD/YYYY'),isnull(week(DD)))	-
001	12
002	1
003	0
004	3
005	5

I am looking for - 

Emp	Week 49	Week 50
002	-	1
004	3	-
005	-	5

Let me know if you have question.

[Taoufiq_Zarra]

can you share your qvf file ?

[Anil_Babu_Samineni]

@LP27 Try this for measure?

sum({<Week={"=Sum(salary)>=0"}>} salary)

[LP27]

This is Qlik sense file attached.