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: 
Anonymous
Not applicable

Expression from SQL query

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?

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

12 Replies
Anil_Babu_Samineni

May be this?

Sum(Aggr(Count({< type_of_activity_id -={8}, deleted = {0} >}, distinct initiate_date), user_id))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mdmukramali
Specialist III
Specialist III

Hi,

Dimension: user_id

=Count({< type_of_activity_id -={8}, deleted = {0} >}Distinct initiate_date)


looks correct.


What is the format of the initiate_date?

share the sample file or screenshot the initiate_date?



Anonymous
Not applicable
Author

initiate_date's format is datetime. Could this be the issue?

sunny_talwar

My response here:

MySQL to Qlikview Expression translation

May be this

Dimension

User

Date(Floor(initiated_date)) -> Calculated dimension

Expression

Count(DISTINCT initiated_date)

This is useful assuming initiated_date is a timestamp... else all you will get is 1 for each row

Anonymous
Not applicable
Author

initiated_date is datetime format. And yes, I am getting 1s for each row.

sunny_talwar

So for a single user, do you have more than one timestamp in a day? For instance user xyz have 01/10/2018 8 am and 01/10/2018 10 am?

Anonymous
Not applicable
Author

Yes, there is a possibility that one user may have multiple timestamps in a day. Now that I look in the pivot table, there are users with more than one timestamps in a day and I assume my Count expression is counting the multiple timestamps too.
So the issue is with the initiated_date formatting?

sunny_talwar

What is the issue? I am not sure I understand?

You want to see user and Date and count the number of timestamps for a user on a date, right? then this should work?

Dimension

User

Date(Floor(initiated_date)) -> Calculated dimension

Expression

Count(DISTINCT initiated_date)

Anonymous
Not applicable
Author

Sorry, I probably didn't expressed myself properly.
I want to see user and count the distinct number of dates, disregarding the time component (I'm doing it in the loading script), for each user, in the pivot table.