Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data (just one table, all columns in it are dimensions, and it is identical to one table from the data model😞
and on sheet I createed a new table that has MonthYear
as dimension and one Count
measure:
Count(distinct [auth_user_id])
However, values of MonthYear
dimension are not grouped. For example, you can see multiple "Nov 2023":
Do you know why dimension values are not grouped? As far as I understood how Qlik works, in this case they should be grouped, there should be only one "Nov 2023" which will show the total number of different users for that MonthYear value, right? 🤔
Can you help me to solve this so that in this case the values of the dimension "MonthYear" are grouped?
MonthStart makes it the1st of the month, but only shows the Month and year.
Originally it was keeping the Day, but not presenting it.
How did you create your MonthYear Dimension ?
These are the parts of the load-scripts that show how "MonthYear" is created:
[Relevant Activities]:
LOAD
users_auth.id AS [auth_user_id],
Date(Floor(users_auth.created_at), 'YYYY-MM-DD') AS [Activity Date],
Date(Floor(users_auth.created_at), 'MMM YYYY') AS [MonthYear],
FROM [lib://QVDFOLDER/users.qvd] (qvd);
...
CONCATENATE([Relevant Activities])
LOAD
auth_user_id AS [auth_user_id],
Date(Floor(payments.created_at), 'YYYY-MM-DD') AS [Activity Date],
Date(Floor(payments.created_at), 'MMM YYYY') AS [MonthYear],
RESIDENT [payments];
...
CONCATENATE([Relevant Activities])
LOAD
auth_user_id AS [auth_user_id],
Date(Floor(messages.created_at), 'YYYY-MM-DD') AS [Activity Date],
Date(Floor(messages.created_at), 'MMM YYYY') AS [MonthYear],
RESIDENT [costs];
... and so on
So, maybe the issue is because we have multiple concatenations? Or maybe the issue is the way we create "MonthYear": Date(Floor(messages.created_at), 'MMM YYYY') AS [MonthYear]
?
----------------------------------------------------------------------------------------------------------------------------------
UPDATE: The problem is definitely with the load-script, because when I added only the "MonthYear" dimension, without any measure, it can be seen that it is not grouped (the image below shows multiple "Nov 2018"):
But I don't understand why it is not grouped, why there isn't just one "Nov 2018"? 🤔
Yes, because even though it shows only MMM YYYY, it is still storing the full date.
Try this:
Date(MonthStart(messages.created_at), 'MMM YYYY') AS [MonthYear],
When I changed from:
Date(Floor(messages.created_at), 'MMM YYYY') AS [MonthYear]
to:
Date(MonthStart(Date(Floor(messages.created_at), 'YYYY-MM-DD')), 'MMM YYYY') As [MonthYear],
Now it works 🙂
I'm not sure why, but I assume that when I use the first way, then even though it shows "Nov 2018" - Qlik treats each "Nov 2018" as a separate date (but if that's the case, why wasn't there more "Nov 2018"?).
When I use another way to create "MonthYear", MonthStart()
function makes all dates from Month/Year equal.
MonthStart makes it the1st of the month, but only shows the Month and year.
Originally it was keeping the Day, but not presenting it.