Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to get an expression to do the same thing in a pivot table as an SQL query.
The dimension I'm using in the said pivot table is user_id.
So, the query is:
SELECT DISTINCT user_id,date(initiate_date),
count(distinct date(initiate_date))
FROM Activity
WHERE
deleted = 0
and type_of_activity_id != 8
Group By user_id;
I've tried with:
Count({< type_of_activity_id -={8}, deleted = {0} >}, distinct initiate_date) but the results of the said expression are not correct for some users.
My question is: Is there any other way of writing the said expression? Am I missing something?
Then try this
Dimension
User
Expression
Count(DISTINCT Floor(initiated_date))
disregarding the time component (I'm doing it in the loading script)
How are you doing this? Are you using Just Date() function or Are you using Date(Floor()) functions? The reason I ask this is because a lot of the time users think that Date() function will truncate the time component, but it is not true. Date function is just a formatting function... it will display you as a date, but the underlying will still have time in it. In order to remove time, you need to use Floor() or you can use DayName() function to get rid of the time part and give it a format all at once
......I was just using the Date() function. God, thanks a lot man, you got me out of a problem I had to deal with for a couple of days!