Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Shinigaloo
Contributor III
Contributor III

The last occurrence of the value of a group dimension

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

 

Labels (4)
8 Solutions

Accepted Solutions
edwin
Master
Master

See Sunny Talwar's post:

Max Date per Month - Qlik Community - 1527312

Attached is an extension of that:

View solution in original post

edwin
Master
Master

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

View solution in original post

edwin
Master
Master

edwin
Master
Master

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

View solution in original post

edwin
Master
Master

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

View solution in original post

Shinigaloo
Contributor III
Contributor III
Author

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

Shinigaloo_0-1607531596798.png

and it works

View solution in original post

edwin
Master
Master

edwin
Master
Master

29 Replies
edwin
Master
Master

See Sunny Talwar's post:

Max Date per Month - Qlik Community - 1527312

Attached is an extension of that:

bullock456
Contributor
Contributor

 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
Contributor III
Contributor III
Author

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)

Shinigaloo_0-1607502802357.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
Master
Master

@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
Contributor III
Contributor III
Author

@edwin can you please help me ? 

edwin
Master
Master

@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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

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?