topic Re: Display 4 weeks data in pivot table in Qlik Sense in App Development

Display 4 weeks data in pivot table in Qlik Sense

LP27 — Sat, 16 Nov 2024 19:29:41 GMT

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.

Re: Display 4 weeks data in pivot table

Taoufiq_Zarra — Tue, 10 Dec 2019 08:14:02 GMT

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,

Re: Display 4 weeks data in pivot table

LP27 — Tue, 10 Dec 2019 08:22:32 GMT

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!

Re: Display 4 weeks data in pivot table

Taoufiq_Zarra — Tue, 10 Dec 2019 08:28:40 GMT

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?

Re: Display 4 weeks data in pivot table

LP27 — Tue, 10 Dec 2019 09:19:51 GMT

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.

Re: Display 4 weeks data in pivot table

Anil_Babu_Samineni — Tue, 10 Dec 2019 10:11:55 GMT

I am using @Taoufiq_Zarra expression

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

Re: Display 4 weeks data in pivot table

LP27 — Tue, 10 Dec 2019 10:58:31 GMT

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.

Re: Display 4 weeks data in pivot table

Taoufiq_Zarra — Tue, 10 Dec 2019 11:45:04 GMT

can you share your qvf file ?

Re: Display 4 weeks data in pivot table

Anil_Babu_Samineni — Tue, 10 Dec 2019 11:49:21 GMT

@LP27 Try this for measure?

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

Re: Display 4 weeks data in pivot table

LP27 — Tue, 10 Dec 2019 11:53:38 GMT

This is Qlik sense file attached.

Re: Display 4 weeks data in pivot table

LP27 — Tue, 10 Dec 2019 12:08:41 GMT

No Luck with the Expression.

😞

Re: Display 4 weeks data in pivot table

Taoufiq_Zarra — Tue, 10 Dec 2019 12:50:26 GMT

try this version,

I changed the table pivot to a simple table

the formula is quite long, but I think it answers your need

Re: Display 4 weeks data in pivot table in Qlik Sense

sunny_talwar — Tue, 10 Dec 2019 13:07:50 GMT

May be this?

Sum({<EmpNo = {"=Count({<Week = {[>=$(=Max(Week)-1)<=$(=Max(Week))]}>} EmpNo) = 1"}, Week = {">=$(=Max(Week)-1)<=$(=Max(Week))"}>}Sales)

Re: Display 4 weeks data in pivot table in Qlik Sense

Taoufiq_Zarra — Tue, 10 Dec 2019 13:15:22 GMT

elegant @sunny_talwar formula as usual,
but in case we are in week 1 of the year week -1 must be 52?
I'm still learning 😉

Re: Display 4 weeks data in pivot table in Qlik Sense

sunny_talwar — Tue, 10 Dec 2019 13:31:20 GMT

You are right... I would rather use WeekYear instead of Week field to address situations like those.

Re: Display 4 weeks data in pivot table in Qlik Sense

LP27 — Tue, 10 Dec 2019 14:02:21 GMT

I tried with my dataset but was not successful in getting the output.

I am looking for the last 4 week data actually. ( I am trying to see Emp who has Dash's in Actual Time for 4 weeks in row)

I am sharing the App below.

let me know your views.

Re: Display 4 weeks data in pivot table in Qlik Sense

sunny_talwar — Tue, 10 Dec 2019 14:05:33 GMT

What is the expected output now?

Re: Display 4 weeks data in pivot table in Qlik Sense

LP27 — Tue, 10 Dec 2019 14:13:58 GMT

I am expecting to display-

The List of Employees with Dash's or hyphen's for the last 4 weeks in [Actual Time] Measure.

something like this below-

Emp ID	W53	W52	W51	W50
00001	-	8	-	7
00002	-	-	-	-
02000	8	8	-	-

Re: Display 4 weeks data in pivot table in Qlik Sense

sunny_talwar — Tue, 10 Dec 2019 14:28:11 GMT

Why do you have Fin and Book table not connected to each other?

Re: Display 4 weeks data in pivot table in Qlik Sense

LP27 — Tue, 10 Dec 2019 14:46:42 GMT

Sorry @sunny_talwar

I guess I missed the connection.

Please find the updated App attached below.

Thanks!