This article demonstrates two methods to count the number of days that passed from one event to the other for a specific category (i.e number of days passed between one sale to another by sale code). The preferred way to do this calculation is in a custom sql table inside the ElastiCube.
If the sale type is [Code] and sale date is [DATE SOLD] and the table is [daily sales by code] follow either of the two methods below:
Method 1
The first method is joining the table to itself when the date field in the first table is unchanged and the date field in the second table is the smallest value that is larger than the first date (this is how we manage the dates to be in the correct order). The other condition here is that the code should be the same in both tables.
SELECT DISTINCT a.[code],
a.[date sold],
Min(b.[date sold]) AS Next,
Daydiff(Min(b.[date sold]), ( a.[date sold] )) AS Days
FROM [daily sales by code] AS a
JOIN [daily sales by code] AS b
ON a.[code] = b.[code]
AND a.[date sold] < b.[date sold]
GROUP BY a.[code],
a.[date sold]
ORDER BY a.[code],
a.[date sold] Method 2
The second method is using a rank function to number the dates in ascending order under each code, then calculating the difference between two dates again using self-join when one rank number is larger than the other by 1.
SELECT c.[code],
c.[date sold],
d.[date sold] AS Next,
Daydiff(c.[date sold], d.[date sold]) Days
FROM (SELECT a.[code],
a.[date sold],
Rankasc(a.[code], a.[date sold]) AS row
FROM [daily sales by code] a) AS c
JOIN (SELECT b.[code],
b.[date sold],
Rankasc(b.[code], b.[date sold]) AS row
FROM [daily sales by code] b) AS d
ON ( c.[code] = d.[code] )
AND ( c.row - d.row = 1 )
ORDER BY c.[code],
c.[date sold] Both methods will return the same result:
After creating a custom table with one of the suggested queries, build schema changes and create your dashboard. Check the results and compare to the data source.
