Set Analysis

[davenyrfajutag00]

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

[Anil_Babu_Samineni]

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

[davenyrfajutag00]

Thank you for your response,

attached you will have the excel file you requested.

[theoat]

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.

[davenyrfajutag00]

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

[theoat]

Can you put an example ?