The last occurrence of the value of a group dimension

[Shinigaloo]

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 !! 

[edwin · Accepted solution]

See Sunny Talwar's post:

Max Date per Month - Qlik Community - 1527312

Attached is an extension of that:

[bullock456]

 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.

[Shinigaloo]

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

[edwin]

@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

[Shinigaloo]

@edwin can you please help me ? 

[edwin · Accepted solution]

@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?

[Shinigaloo]

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 ?

[Shinigaloo]

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 ?

[edwin]

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?