Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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 🙂
sum({<date={"=Aggr(Max(FQST_Date_Stock),[Année])"},[TypeDate]={'DateStock'},[FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
Sorry Chenna,
it doesn't give the right result
thank you for the help 🙂
Try this:
=sum({<date={"$(=Max(FQST_Date_Stock))"},[TypeDate]={'DateStock'}, [FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
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)
)
Hello Sunny,
thank you for the response it hilights the last year-month for each year.
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
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
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
But when I delete the date.Date dimension letting just Année it gives me a null result
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
Try this
FirstSortedValue({<[TypeDate] = {'DateStock'}, [FQST_Type] = {'StockInterne'}>} Aggr(
Sum({<[TypeDate] = {'DateStock'}, [FQST_Type] = {'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
, Date), -Date)