topic Re: Why are dimension values not grouped? in App Development

Why are dimension values not grouped?

RoyBatty — Fri, 10 Nov 2023 00:00:41 GMT

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?

Re: Why are dimension values not grouped?

Lisa_P — Fri, 10 Nov 2023 00:34:43 GMT

How did you create your MonthYear Dimension ?

Re: Why are dimension values not grouped?

RoyBatty — Fri, 10 Nov 2023 02:16:59 GMT

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

Re: Why are dimension values not grouped?

Lisa_P — Fri, 10 Nov 2023 02:15:47 GMT

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

Re: Why are dimension values not grouped?

RoyBatty — Fri, 10 Nov 2023 02:37:51 GMT

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.

Re: Why are dimension values not grouped?

Lisa_P — Fri, 10 Nov 2023 02:40:27 GMT

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

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