topic Re. :Re: Set analysis Syntax in QlikView

Set analysis Syntax

alex59800 — Tue, 15 Dec 2009 15:29:18 GMT

Hi there,

I am quite a beginner in set analysis syntax so i would need your help:

I have two dimensions i want to use in a set analysis expression to format my table:

-Distribution networks (going from 01 to 22 in text format)

-Analytics families (going from 98510 to 98599 in numeric format)

I use ONLY(YEAR) and ONLY(YEAR)-1 to select variable periods following monthly and daily Sales.

With distribution networks and analytics families in dimension, my first expression column is:

FIRST COLUMN to traduce turnover for ANALYTICS >98518 and <98527 in distribution networks 01 and 07 (it's working maybe there is more simple expression ...???):

=sum({<YEAR={$(=ONLY(YEAR)-1)} [ANALYTICS] = {'< 98518'} [DISTRIB NETWORKS]={'07'} >} [SALES]) + sum({<YEAR={$(=ONLY(YEAR)-1)} [ANALYTICS] = {'< 98518'} [DISTRIB NETWORKS]={'01'} >} [SALES])+
sum({<YEAR={$(=ONLY(YEAR)-1)} [ANALYTICS] = {'> 98527'} [DISTRIB NETWORKS]={'07'} >} [SALES]) + sum({<YEAR={$(=ONLY(YEAR)-1)} [ANALYTICS] = {'> 98527'} [DISTRIB NETWORKS]={'01'} >} [SALES])

SECOND COLUMN to traduce turnover for ANALYTICS <98518 in distribution networks different from 01 and 07 (I NEED HELP on that)

THIRD COLUMN to traduce turnover for ANALYTICS from 98518 included to 98521 included whatever the distribution network is. (I NEED HELP on that)

FOURTH COLUMN to traduce turnover for ANALYTICS from 98522 included to 98527 included whatever the distribution network is. (I NEED HELP on that)

thanks guys

Set analysis Syntax

Miguel_Angel_Baeyens — Tue, 15 Dec 2009 16:02:50 GMT

Hello Dauchy


Dauchy wrote:FIRST COLUMN to traduce turnover for ANALYTICS >98518 and<98527 in distribution networks 01 and 07 (it's working maybe thereis more simple expression ...???):

Actually, I'd try something (untested but in any case, very similar), to:

=sum({<YEAR={$(=ONLY(YEAR)-1)} [ANALYTICS] = {">98518<98527"}, [DISTRIBUTION NETWORK]={"01","07"} >} [SALES])

By the way, I see [ANALYTICS] field with two different ranges of values: 98000 and 01/07 so I understand that you mean

[DISTRIBUTION NETWORK]='01'

instead of

[ANALYTICS]='01'

Let me know if I'm wrong.


Dauchy wrote:
SECOND COLUMN to traduce turnover for ANALYTICS <98518 in distribution networks different from 01 and 07 (I NEED HELP on that)

=sum({<YEAR={$(=ONLY(YEAR)-1)} [ANALYTICS] = {"<98518"}, [DISTRIBUTION NETWORK]-={"01","07"} >} [SALES])


Dauchy wrote:
THIRD COLUMN to traduce turnover for ANALYTICS from 98518 included to 98521 included whatever the distribution network is. (I NEED HELP on that)

=sum({<YEAR={$(=ONLY(YEAR)-1)} [ANALYTICS] = {">=98518<=98521"} >} [SALES])


Dauchy wrote:FOURTH COLUMN to traduce turnover for ANALYTICS from 98522 included to 98527 included whatever the distribution network is. (I NEED HELP on that)

=sum({<YEAR={$(=ONLY(YEAR)-1)} [ANALYTICS] = {">=98522<=98527"} >} [SALES])

Hope this helps

Re. :Re: Set analysis Syntax

alex59800 — Tue, 15 Dec 2009 16:13:40 GMT

Hi miguel

You're totally right, i made a mistake by translating my expression from french to english. I corrected it. 01 and 07 are for distribution networks, not for analytics. I'm gonna test your syntax and i 'll be back soon.

Thax a lot for your help

Re. :Re: Set analysis Syntax

alex59800 — Tue, 15 Dec 2009 16:48:00 GMT

Well thx a lot Miguel, your syntax just work fine. Very helpful !!!

Alex