topic Re: The last occurrence of the value of a group dimension in QlikView

The last occurrence of the value of a group dimension

Shinigaloo — Sat, 16 Nov 2024 17:43:34 GMT

Hello all,

I want to be able to access to a detail distribution of my activated devices by type on a given period of time

What have been done : i have created a bar chart that display the number of activeated device by device [Catégorie equipement ] and its type [Modèle équipement ] ( see the attached picture)

Measure : =Count({($<activation_eqt= {">=$(=$(vDateDebFormat))"}>) * ($<activation_eqt= {"<=$(=$(vDateFinFormat))"}>)} distinct ([ID_EQT])) Dimension : group dimension [Device]

Now the probelem is that for [Modèle équipement ], i want to display only the last occurrence of this field on a given period

I’ve tried with this measure but it doesn’t worked : =Count({<[Modèle équipement] = {"$(=FirstSortedValue([Modèle équipement],-Aggr(max({<activation_eqt= {">=$(=$(vDateDebFormat))"}> * <activation_eqt= {"<=$(=$(vDateFinFormat))"}>}activation_eqt), [Modèle équipement])))"}>} DISTINCT [ID_EQT])

exp :

user1 / categoriA / Modèle équipement AA /12/12/2020

user1 / categoriA / Modèle équipement bb/13/12/2020

I would like to display the occurrence with the largest date on the histogram not on the script :

user1 / categoriA / Modèle équipement bb/13/12/2020

Could you help me plz it's very urgent.

thx you !!

Re: The last occurrence of the value of a group dimension

edwin — Tue, 08 Dec 2020 22:41:34 GMT

See Sunny Talwar's post:

Max Date per Month - Qlik Community - 1527312

Attached is an extension of that:

Re: The last occurrence of the value of a group dimension

bullock456 — Wed, 09 Dec 2020 07:25:08 GMT

It seems like Max(Date#(Date,'YYYY')) isn't a valid dimension. I want the values of my chart to be 1 for 2014, 1 for 2015 and 2 for 2017. know how to use the last occurrence instead of the first for a distinct count. aggr works like a group by in the script in your case you must group the max date by ID.

Re: The last occurrence of the value of a group dimension

Shinigaloo — Wed, 09 Dec 2020 08:35:04 GMT

in the example below Edwin , you have used category and model as 2 dimensions , but in my case i have one groupes dimension (hierarchical dimemsion )[Device] that contains the 2 dimension (see attached png)

i'm using a bar chart graph that will diplay the number of equipement by category , and then when you clik into one category , you will have the detail of each model

Now as i said i want to only have the last occurence of model dimension

Re: The last occurrence of the value of a group dimension

edwin — Wed, 09 Dec 2020 12:40:02 GMT

@bullock456 the principle is the same whether you group by Month, Category, Year. here is an example in line with the same solution from Sunny:

Re: Max date per month for 6 months dynamic in Bar... - Qlik Community - 1766588

re It seems like Max(Date#(Date,'YYYY')) isn't a valid dimension were you referring to your own data model? if this is a new question, you should open a new thread

Re: The last occurrence of the value of a group dimension

Shinigaloo — Wed, 09 Dec 2020 12:45:02 GMT

@edwin can you please help me ?

Re: The last occurrence of the value of a group dimension

edwin — Wed, 09 Dec 2020 12:46:14 GMT

@Shinigaloo the grouping is a drill down. selecting a category will display the model but a category is still selected. when you added the group, did it work the way you expected?

Re: The last occurrence of the value of a group dimension

Shinigaloo — Wed, 09 Dec 2020 13:07:50 GMT

yes , it looks great now, meanwhile i have another problem is that i have to do this calcul for each user , it means that user should be involved into sum expression ?

Re: The last occurrence of the value of a group dimension

Shinigaloo — Wed, 09 Dec 2020 13:57:16 GMT

i've updated the .qvw document so that i've added one more user , so now how can i handle this situation , i have to do the same work but for each user ?

Re: The last occurrence of the value of a group dimension

edwin — Wed, 09 Dec 2020 14:58:46 GMT

i do see the new user but i do see its behaving per specification max date per category. did you mean that it should be max date per category and user? regardless of model?

Re: The last occurrence of the value of a group dimension

Shinigaloo — Wed, 09 Dec 2020 15:09:47 GMT

re Edwin ,

it should be the last occurrence for a model per user, and category

Re: The last occurrence of the value of a group dimension

edwin — Wed, 09 Dec 2020 15:22:04 GMT

just expand teh AGGR then

Re: The last occurrence of the value of a group dimension

edwin — Wed, 09 Dec 2020 15:24:30 GMT

Re: The last occurrence of the value of a group dimension

Shinigaloo — Wed, 09 Dec 2020 15:45:53 GMT

ohhh thank you edwin it's worked finally ,

now i have another issue edwin , ive converted your qvw to a bar chart ,and i still have the titles of all the models even if they are empty how display only the titles of last models

here is the qvw document updated

Re: The last occurrence of the value of a group dimension

edwin — Wed, 09 Dec 2020 16:02:37 GMT

that is how the bar chart works, it will show all the values of the dimensions. so if model A appears in one date but not in another, it will show it as 0 in a bar chart

Re: The last occurrence of the value of a group dimension

edwin — Wed, 09 Dec 2020 16:05:24 GMT

also, i dont think a bar chart is the best presentation for this. once you get a lot of models and dates, the chart will be very busy. this should be a straight table

Re: The last occurrence of the value of a group dimension

Shinigaloo — Wed, 09 Dec 2020 16:29:25 GMT

thank you so much edwin , for your responses

Re: The last occurrence of the value of a group dimension

Shinigaloo — Wed, 09 Dec 2020 16:33:29 GMT

i think it's possible to hiden it ive made this

and it works

Re: The last occurrence of the value of a group dimension

edwin — Wed, 09 Dec 2020 16:53:05 GMT

awesome

Re: The last occurrence of the value of a group dimension

Shinigaloo — Thu, 10 Dec 2020 09:33:16 GMT

i have another evolution that should be taken into account in my document qvw

my date vary depending on the selected period, it should be date < vdatefinformat and date >vdatedebformat

How can i apply this condition into my expression below

Thank you