Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

Date Difference in pivot Table by Dimension Values

Hello,

I have a pivot table that looks like below:

TypeStart DateEnd Datediff
T11/9/20201/17/2020-
T11/18/20201/23/20201
T11/25/20201/27/20202
T22/1/20202/4/20205
T22/6/20202/9/20202
T32/11/20202/13/20202

 

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:

TypeStart DateEnd Datediff
T11/9/20201/17/2020-
T11/18/20201/23/20201
T11/25/20201/27/20202
T22/1/20202/4/2020-
T22/6/20202/9/20202
T32/11/20202/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 @tresesco @sunny_talwar 

Labels (2)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

You can just include an if clause like:

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

View solution in original post

2 Replies
tresesco
MVP
MVP

You can just include an if clause like:

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

mrthomasshelby
Creator III
Creator III
Author

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 @tresesco ! Appreciate it.