Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use a boolean set analysis

Hi All,

I have a table with several quotations made along these years. Each quotation can have zero, one or more revisions.

I need to sum the quotation value by month.

The analysis is based on some criteria:

  1. The key element is the month when the quotation has been sent (field=offer sent by data) (Offer can be prepared in February and then sent in March, so the total has to be summed in March report)
  2. Only the last revision (off_rev) has to be used for the total amount. (If off_rev = 0 it is the quotation itself otherwise I take the last valid revision and previous ones are excluded). In case a same quotation starts and is modified within the same month, then this last revision is taken on the total.
  3. In case a same quotation starts in a previous month (for instance January) and it has been sent still in a previous month (February) and then this current month (March) it is revised again then I need to exclude it to avoid repetitive sum of same quotation. (As it has been already included in the total of February.

This last point is hard to solve as I can trace the last valid version of quotation (there a field named REV which shows "NO" if it is the last revision) but I do not know how to exclude this same quotation in the next month in case it should be revised and send again.

Can someone help me with this?

I was thinking to the set analysis but I do not know how to set properly the formula.

Massimo

2 Replies
ajsjoshua
Specialist
Specialist

Hi,

Please come up with same Excel sample.

Regards,

Joshua.

Not applicable
Author

Hi Joshua,

this is an example created on Excel just to better explain my needs.

Actual spreadsheet is very longer than this so I cannot analyse "by hand"

Massimo