Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
faroukfrk
Contributor III
Contributor III

Max Date for each year

Hello guys,

I want to ask you about making a  set analysis  that calculates the sum of stock value at  "the Max Date"  for each year

supposing that I have the stock value for the 2 years 2018 and 2019

i want to get the stock value for both max 2018  "31-12-2018"and 2019  "31-03-2019"

I have written a set analysis like this

Aggr(sum({<date={"=Aggr(Max(FQST_Date_Stock),[Année])"},[TypeDate]={'DateStock'},[FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant),[Année])

but it gives me the value of Jan-2018 and Jan-2019

like in the picture :

 

Capture.PNG

for Jan-2019 it's the value of '01-01-2019'

for Jan-2018 it's the value of '31-01-2018' (the only date in Jan-2018 the system began from that date)

thank you for helping me cherished community  🙂

 

Labels (2)
10 Replies
Channa
Specialist III
Specialist III

sum({<date={"=Aggr(Max(FQST_Date_Stock),[Année])"},[TypeDate]={'DateStock'},[FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)

Channa
faroukfrk
Contributor III
Contributor III
Author

Sorry Chenna,

it doesn't give the right result

 

thank you for the help 🙂Capture2.PNG

 

Marcos_rv
Creator II
Creator II

Try this:

 

=sum({<date={"$(=Max(FQST_Date_Stock))"},[TypeDate]={'DateStock'}, [FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant) 

sunny_talwar

Try this may be

If([Année-Mois] = Max(TOTAL <Année> [Année-Mois]),
Sum(TOTAL <Année> {<[TypeDate] = {'DateStock'}, [FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
)
faroukfrk
Contributor III
Contributor III
Author

Hello Sunny,

thank you for the response it hilights the last year-month for each year.

 

Capture3.PNG

 

 

However, is there anything else  to add so that it gives me "the stock value" of  last date for each month  i mean "31-12-2018"  and "31-03-2019"

we are almost there 😉

PS Would you please explain me what does that set analysis do and the meaning of <Année> does that mean "over years" ?!

Thank you a lot

Channa
Specialist III
Specialist III

If([Année-Mois] = Max(TOTAL <Année> [Année-Mois]),
Sum(TOTAL <Année> {<[TypeDate] = {'DateStock'}, [FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
)

 

IF YOU REMOVE TOTAL FROM THE EXPRESSION YOU WILL GET VALUE OF THE DATE 

Channa
faroukfrk
Contributor III
Contributor III
Author

Hello again,

I did this change to the set analysis that you've proposed @sunny_talwar 

 

If([date] = Max(TOTAL <Année> [date]),
Sum( {< date = {"=Max(TOTAL <Année> [date])"},[TypeDate] = {'DateStock'},  [FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
)

and it gives me the right calculation  for the dates 31-12-2018 and 31-03-2019 as in the picture below

 

 

Capture5.PNG

 

But when I delete the date.Date dimension letting just Année it gives me a null result 

 

Capture6.PNG

 

faroukfrk
Contributor III
Contributor III
Author

Nope @Channa ,

that gives a Null Value :'(

thanks for trying

you would see the modification I made to @sunny_talwar 's expression

it's almost there I think I need some tip that makes a year represented by its max date instead

Thank you

sunny_talwar

Try this

FirstSortedValue({<[TypeDate] = {'DateStock'},  [FQST_Type] = {'StockInterne'}>} Aggr(
Sum({<[TypeDate] = {'DateStock'}, [FQST_Type] = {'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
, Date), -Date)