Solved: Expression from SQL query - Qlik Community

Anonymous · ‎2018-01-10

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?

sunny_talwar · ‎2018-01-10

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

Anil_Babu_Samineni · ‎2018-01-10

May be this?

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

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 · ‎2018-01-10

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 · ‎2018-01-10

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

sunny_talwar · ‎2018-01-10

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 · ‎2018-01-10

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

sunny_talwar · ‎2018-01-10

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 · ‎2018-01-10

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 · ‎2018-01-10

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 · ‎2018-01-10

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.