Total with set analysis

[rcandeo]

Hi, I have this expression:

if(sum(Quantity_budget) > 0, sum({<Salesman = {'John'}>} Quantity_Sales),0)

where i intend to show the amount of sales, only for the SKUs that are on the budget.

It Works, but I need the total, and if I put the SKU to summarize in the dimension, the total summarizes all the lines, not only the ones that have budget.

Anyone can give me a hint please?

Robson

[jpapador]

Cesar almost had I think.

sum({$<Quantity_budget-={'0'},Salesman = {'John'}>} TOTAL Quantity_Sales)

You need the dash before the equals and after budget to exclude values = to 0 in budget.

[rcandeo]

No, some don´t have budget. That´s my problem.

[rcandeo]

I don´t know why I get only zeroes with this expression

[jpapador]

At this point I think it may be easier to do in the script.  Try this:

Left Join (to the table with SKUs)

LOAD

KeyFromSKUsTable,

SKU as SKU2

Resident SKU Table

Where Quantity_budget > 0 and

Len(Quantity_budget) > 0;

That will give you a second SKU field that has a budget associated that isnt null or 0.  Then your expression can look like:

Sum({$<Salesperson={'John'}>}TOTAL SKU2)

[calvindk]

Can you upload a qvw for us to see, just a data reduced one with an example of the not working part?

[rwunderlich]

Try changing your total mode to "Sum of rows" instead of Expression Default.

Rob