topic Re: Date Difference in pivot Table by Dimension Values in QlikView

Date Difference in pivot Table by Dimension Values

mrthomasshelby — Sat, 16 Nov 2024 01:33:24 GMT

Hello,

I have a pivot table that looks like below:

Type	Start Date	End Date	diff
T1	1/9/2020	1/17/2020	-
T1	1/18/2020	1/23/2020	1
T1	1/25/2020	1/27/2020	2
T2	2/1/2020	2/4/2020	5
T2	2/6/2020	2/9/2020	2
T3	2/11/2020	2/13/2020	2

Where diff column is the difference between the Start Date of current row and End Date of the previous row, which I have used the expression =(([Start Date]) - Above(TOTAL [End Date])). But what I want is the table shown below:

Type	Start Date	End Date	diff
T1	1/9/2020	1/17/2020	-
T1	1/18/2020	1/23/2020	1
T1	1/25/2020	1/27/2020	2
T2	2/1/2020	2/4/2020	-
T2	2/6/2020	2/9/2020	2
T3	2/11/2020	2/13/2020	-

Basically I want the 'diff' column to reset each time the dimension value changes. How do I change my expression to achieve this? Thanks in advance!

@Gysbert_Wassenaar @tresB @sunny_talwar

Re: Date Difference in pivot Table by Dimension Values

tresB — Thu, 09 Jan 2020 12:32:50 GMT

You can just include an if clause like:

If(Type= Above(Total Type), <Your Existing exp>)

Re: Date Difference in pivot Table by Dimension Values

mrthomasshelby — Thu, 09 Jan 2020 12:37:18 GMT

Oh boy! I was trying the same thing: if(Type=Above(Type),Exp) and it wasn't working. Looks like i missed the important TOTAL parameter. Thanks a lot @tresB ! Appreciate it.