topic Set Analysis Doubling YTD Numbers in QlikView

Set Analysis Doubling YTD Numbers

asmithids — Tue, 01 Apr 2014 01:46:10 GMT

Hello,

I have a Container that has a "Comparative Sales Analysis By Customer and Item" straight table that shows a Month sales column and a YTD sales column for the year. The issue is the Month and YTD columns are doubling up. For example, when I filter on the list box Year = 2013 and list box Month = Jan, the Month sales amount equals the YTD amount, but the amounts are doubling. The month sales amount should be $400 and the same for the YTD column. The proof is on the Salesperson Analysis sheet on the straight table called "Sales Rep Orders" I added a Bookmark called "M303-128" as the controlled filter.

My set expression is the issue, but I'm not sure what the problem is. I attached the qvw for reference.

Thank you in advance for any assistance.

Re: Set Analysis Doubling YTD Numbers

Anonymous — Tue, 01 Apr 2014 02:23:25 GMT

Not sure which is right but if you sum quantity in your second table as well it will match your set analysis. If you don't want that the below equation should work.

=count({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} distinct Quantity) * UnitCost

Re: Set Analysis Doubling YTD Numbers

asmithids — Wed, 02 Apr 2014 12:34:07 GMT

Hi Michael,

Thank you for responding. The sum on the second table (i.e. Sales Rep Orders) is the correct amount. The issue is I can't get the "Comparative Sales Analysis By Customer and Item" current monthly sales to sum the total sales for the month. When I include the Reference (i.e. invoice number) in the Comparative Sales Analysis By Customer and Item table I do get the correct monthly sales amount. However, I don't want to include that level of detail on that table. I can't get it to sum all of the invoices to a total. It is just summing the first invoice is finds per customer and is ignoring the other invoices for that customer.

I even tried a simple Sum(Quantity * UnitCost) as the monthly sales calculation, but that expression doubles the current sales amount to $800 when it should be $400 when filtering for 2013, Jan as shown on the Sales Rep Orders table.