topic Re: calculated dimension aggr in QlikView

calculated dimension aggr

Thu, 25 Jul 2013 14:03:25 GMT

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.

Re: calculated dimension aggr

Gysbert_Wassenaar — Thu, 25 Jul 2013 14:19:42 GMT

See attached example.

Re: calculated dimension aggr

Thu, 25 Jul 2013 14:39:50 GMT

thts brilliant ... thanks

Re: calculated dimension aggr

Tue, 30 Jul 2013 14:25:17 GMT

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?

Re: calculated dimension aggr

Tue, 30 Jul 2013 15:13:18 GMT

bump ...

anyone ?

Re: calculated dimension aggr

Gysbert_Wassenaar — Tue, 30 Jul 2013 17:44:41 GMT

Try

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

Re: calculated dimension aggr

Tue, 30 Jul 2013 18:10:14 GMT

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)