Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, Guys Hope you all doing well,
I have a problem with the sum of stock quantities
first of all, here is the header of the main columns in the fact table "Stock Quantity "
"FQST_Stock_Id" , "ENT_Entreprise_Id", "STR_Magasin_Id", "PRD_Produit_Id","EMPL_Emplacement_Id","FQST_Date_Stock" and "FQST_Quantite_Produit", "FQST_Prix_Public"
I want to have the stock value at a selected date for all the products at any enterprise any emplacement (location) and any magazine(store)
I have made this set analysis expression however it gives me just the sum of last date (max date):
Sum({<date={"$(=max(FQST_Date_Stock))"},[TypeDate]={'DateStock'},[FQST_Type]={'StockInterne'}>}
FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
the picture joined with the question may explain more by default and without selecting any date
the result of the measure is the sum of rows in green based on a distinct value of the fields in yellow we get the sum of last values
so if you may help me to get the stock value by fetching the last distinct value of a row composed of the filed mentioned above
PS: it is near in meaning to the SQL instruction "Distinct On"
and it is the result of this SQL query
select * from
(SELECT DISTINCT ON ("ENT_Entreprise_Id", "STR_Magasin_Id","PRD_Produit_Id", "EMPL_Emplacement_Id") t1.*
FROM "BI"."Test" t1
order by "PRD_Produit_Id", "EMPL_Emplacement_Id","ENT_Entreprise_Id","STR_Magasin_Id",
"FQST_Date_Stock" DESC
)t
order by "FQST_Date_Stock" DESC
Thank you
The image rows are blur, Could you please specify the few rows and expected one?
the first table is the input and the green colored rows in the second table are the output
You may zoom on the picture to see it clearly @Anil_Babu_Samineni
you may also take a look at the SQL Query i added at the end it gives the result i want to have
@Michael_Tarallo do you have any idea ?!
how to make a "select distinct on (filed1,filed2)" withing qlik sense using chart functions