Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Display 4 weeks data in pivot table in Qlik Sense

Hi Everyone,

 

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

This is my pivot table-

Emp NoWeek 51Week - 50Week - 49Week - 48Week - 47
001-24-1
003-48-8
004-8--7
002--272

 

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 NoWeek - 50Week - 49
0048-
002-2

 

 Please let me know if you have question.

Thanks in Advance.

 

1 Solution

Accepted Solutions
Highlighted

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?

View solution in original post

25 Replies
Highlighted
Master
Master

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

datt.PNG

(check delete if value is null)

I hope this solution will be okay for you

Cheers,

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Creator II
Creator II

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-

 

EmpWeek 50Week 49
002-1
0043-
005-5

 

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

 

Thanks in advance! 

Highlighted
Master
Master

I didn't quite understand that.


for the example

EmpWeek 50Week 49
002-1
0043-
005-5


what exactly do you want to keep as output?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Creator II
Creator II

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.

EmpWeek 49Week 50
00151
002-1
0043-
005-5

 

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

Like the below as output-

EmpWeek 49Week 50
002-1
0043-
005-5

 

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

 

Highlighted

I am using @Taoufiq_ZARRA  expression

 

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

 

 

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator II
Creator II

Hi Anil,

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

This is what i am getting.

EmpNoif(DD>=Date(today()-7, 'MM/DD/YYYY'),isnull(week(DD)))-
00112
0021
0030
0043
0055

 

I am looking for - 

EmpWeek 49Week 50
002-1
0043-
005-5

 

Let me know if you have question.

Highlighted
Master
Master

can you share your qvf file ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted

@LP27 Try this for measure?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator II
Creator II

This is Qlik sense file attached.