topic IF to Set Analysis in QlikView

IF to Set Analysis

Fri, 18 Jun 2010 15:32:45 GMT

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

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

IF to Set Analysis

fernandotoledo — Fri, 18 Jun 2010 18:40:27 GMT

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

IF to Set Analysis

Fri, 18 Jun 2010 18:52:22 GMT

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

IF to Set Analysis

fernandotoledo — Fri, 18 Jun 2010 19:16:29 GMT

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.

IF to Set Analysis

Fri, 18 Jun 2010 20:47:07 GMT

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?

IF to Set Analysis

johnw — Fri, 18 Jun 2010 22:27:36 GMT

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"))

IF to Set Analysis

Fri, 18 Jun 2010 23:19:49 GMT

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)

IF to Set Analysis

johnw — Fri, 18 Jun 2010 23:27:53 GMT

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

IF to Set Analysis

Sat, 19 Jun 2010 00:17:50 GMT

This seemed to work for me

sum

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