Why are dimension values not grouped?

[RoyBatty]

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?

[Lisa_P]

How did you create your MonthYear Dimension ?

[RoyBatty]

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"? 🤔

[Lisa_P]

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], 

[RoyBatty]

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.

[Lisa_P · Accepted solution]

MonthStart makes it the1st of the month, but only shows the Month and year.

Originally it was keeping the Day, but not presenting it.