calculated dimension aggr

[#1]

i have data for customers and sales by months. requirement is to find new customers, that means: any customer have sales in the latest month and no sales in the past 5 months. i tried to achieve with aggr with Logical AND in calculated dimension, sure i am not understanding the right way to use aggr here.

Please find the attached app.

[Gysbert_Wassenaar]

See attached example.

[#3]

thts brilliant ... thanks

[#4]

Hello Gysbert ...

this is not working in all scenarios .... it gets me the result as new for the below example also.

Month Sales

1    0

2    0

3    0

4    0

5    20

6   10

tried both ways

sum({<Monthserial={$(=max(Monthserial))}>*

        <customer=p({<Sales={0},Monthserial={'>=$(=max(Monthserial)-5)<$(=max(Monthserial)))'}>}customer)>}

        Sales)

sum({<customer=p({<Sales-={0},Monthserial={$(=max(Monthserial))}>}[customer])

* p({<Sales={0},Monthserial={">=$(=max(Monthserial)-5)<$(=max(Monthserial)))"}>}customer),

Monthserial={$(=max(Monthserial))}>

}

Sales)

looks like Sales={0},Monthserial={">=$(=max(Monthserial)-5)<$(=max(Monthserial)))"} gets customer who got zero sales in any of the past 5 months.

how make sure it gets customers only when sales for all past 5 months zero?

[#5]

bump ...

anyone ?

[Gysbert_Wassenaar · Accepted solution]

Try

sum({<Monthserial={$(=max(Monthserial))},
      customer={"=sum({<Monthserial={'>=$(=max(Monthserial)-5)<$(=max(Monthserial))'}>}Sales)=0"}>}
    Sales)

[#7]

tht sure worked.... great ...

ididn't even know that we can write set like this. i am lost is how the customer expression is working in the set ?

how does it know to assign list of customers when the expression value is true ? i thought using P (possible) only way.

it will be great if you can explain me the mechanism of how qlikview is handling this set. Thanks for your time in advance. 

sum({<Monthserial={$(=max(Monthserial))},                                 customer={"=sum({<Monthserial={'>=$(=max(Monthserial)-5)<$(=max(Monthserial))'}>}Sales)=0"}>}
    Sales)