IF to Set Analysis

[#1]

How would i convert this IF statement to a set analysis?

(if(Today()-min(Receipt date)<=20, Inventory))

[fernandotoledo]

Depend on what you are trying to do. If Statements and Set Analisys statements have diferent purposes.

[#3]

i'm looking to sum up the inventory for items, where the minimum receipt date was less than 20 days ago.

[fernandotoledo]

Then your IF() is correct and it´s allright use it! You would use set analisys if want to see the result of your calculation for a SET of data different than what is selected.

[#5]

the only problem i have with the IF statement is that i cannot get subtotals. i can only get grand totals in a straight table, by selecting Sum of Rows. Do i need to modify the IF statement?

[johnw]

I thought only a pivot table could give you subtotals. To subtotal in a pivot table:

sum(aggr(your expression,your dimensions))

So perhaps something like this:

sum(aggr(sum(if(today()-min("Receipt Date")<=20,"Inventory")),"Product"))

[#7]

Hi John,

you are right about subtotals in pivot tables only. i had to settle with a straight table once i realized i couldn't get subtotals. i tried your statement, but get back 0s (no matter what i change the <= value to)

[johnw]

If you post an example file, I can make it work. Otherwise, not sure what to tell you.

[#9]

This seemed to work for me

sum

(aggr(num(if(Today()-min(Receipt DATE_CREATE)<=20, sum(Inventory))),Product))