Multiple value selection causes incorrect value, for result from set analysis, not sure why, Please advise.
How can I calculate the difference in time between the selected date and the date before that? for instance if 1/7/2011 is selected, then the time should be sum of all time with date 1/7/2011 - sum of all time with date prior to that, if there is no prior date then the same date should be treated as prior date, for instance 1/1/2011 with have the previous date as 1/1/2011.
when 1/1/2011 is selected diff should be 0 when 1/7/2011 is selected diff should be 7 (18-11) when 1/14/2011 is selectd diff should be 2 (20-18)
when 1/14/2011 and 1/7/2011 is selected diff should be 7+2=9 when all the three dates are selected the diff should be 0+7+2 = 9
when more than one date is selected the value calculated through set analysis is incorrect, but when one value is selected, correct value is calculated
My sample file is attached.
Date selected - calculated diff
1/1/2011 - 0 1/7/2011 - 52 1/14/2011 - (-10)
But when all the three dates are selected, the diff should be 42 instead it shows as 148
This is because, sum(106+158+148) - sum(106+158) which is what my formula is.
formula used : sum({$<Date=P(Date)>} Time)-sum({$<Date=P(PrevDate)>} Time)
But I would like to do sum(Time) - sum(prevTime) using set analysis for each selected date, However date is not a dimension in the pivot table.
