Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good evening,
I would like to ask support for using Set Analysis in a table object in Qlik Sense.
Attached you can see a table with these following fields:
1st field - INVOICE DATE
2nd field - ITEM DATE COST INPUT
3rd field - field forced to last date inputted in 2nd field (in this example 01/12/2023)
4th field - ITEM COST
5th field - ITEM Qty
6th field - Set Analysis field
In my set analysis field, the 6th one, I would like to SUM (the ITEM COST based on last date inputted in 2nd field times * ITEM Qty)
So for example:
1row 9,551297 * 20 = 191,022594 (note that second column and third column have the same date)
2row 9,551297 * 40 = 382,95188 (note that second column and third column have the same date)
3row at the moment it is 9,567545 * 20 = 191,3509 but as I would like it be, it should be 9,551297 * 20
4row and the other rows should consider 9,551297 (the ITEM COST based on last date inputted in 2nd field)
How can I implement this formula as set analysis? Or do you have any other suggestions?
Thank you in advance.
Kind Regards
@davenyrfajutag00 Please extract the same report in excel that you attach in image, we can try something for you!
It i understand correctly, You want to Sum the value where second and third column is same and otherwise It must pick the previous once?
Thank you for your response,
attached you will have the excel file you requested.
Good afternoon,
To resolve your case, you can use the calculated variable. I named it"vCOSTO_CURRENTE".
You need to enter this formula in the variable expression :
={<AC_DATA_COSTO_INI={"$(=ULTIMA_DATA_COSTO_ART)"}>}COSTO_ART_VALORE
It saves the cost at the last capture.
Next, you need to go in the sixth field of your table and enter this expression :
=sum(vCOSTO_CORRENTE)*sum([SUM(M_QTA_DOC_UM_PRIM)])
I hope that it is clear and that answers your question.
Kind regards,
Théo ATRAGIE.
Good afternoon Theo,
thank you for your response.
I will try this way and let you know!
I have a question:
{<AC_DATA_COSTO_INI={"$(=ULTIMA_DATA_COSTO_ART)"}>}COSTO_ART_VALORE saves the cost at the last capture. What should I do to pick the cost at the last capture for every item sold?
Thank you in advance
Can you put an example ?