Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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,
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!
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?
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.
I am using @Taoufiq_Zarra expression
=if(DD>=Date(today() -7, 'MM/DD/YYYY'), IsNull(week(DD)))
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.
can you share your qvf file ?
@LP27 Try this for measure?
sum({<Week={"=Sum(salary)>=0"}>} salary)
This is Qlik sense file attached.