topic CONDITIONAL SUM in QlikView

CONDITIONAL SUM

Wed, 26 Jan 2011 12:58:20 GMT

hI folks!!

I have three tables:

Table 1: MES_BALANCE, AÑO, MES, ID

Table 2: SOCIEDAD, CUENTA, MES_BALANCE, IMP_DIARIO, ID_SOCCTA

Table 3: ID_SOCCTA, IMP_ANALITICA

I select information by selecting AÑO and MES (YEAR and MONTH)

Could I sum acumulate imp_analitica until month and year selected?

I create Table 1 exclusively to get an ID and sum it like: SUM({$<ID={'<=$(=ID)'} >} IMP_ANALITICA)

No data appears because ID is not a selected field, so I tried SUM({$<ID={'<=$(=Max(ID))'} >} IMP_ANALITICA)...

and wrong data.

Anyone can help me? I appreciate your help because I,m going crazy

Fiber9906

CONDITIONAL SUM

Wed, 26 Jan 2011 13:27:28 GMT

First of all, click on Table Viewer to check if the relationship between the tables is ok.

CONDITIONAL SUM

Wed, 26 Jan 2011 13:32:56 GMT

I think it is, but I am reviewing constanly. Has it sense what I´m saying, isn´t it?

CONDITIONAL SUM

Anonymous — Wed, 26 Jan 2011 13:40:22 GMT

Can you share the file with us?

CONDITIONAL SUM

Wed, 26 Jan 2011 13:43:23 GMT

Is the 'ID' of table 1 the same of 'ID_SOCCTA' of table 2 and 3?

Because if not, then you'll create something to conect the two tables, at leas I guess it's this.

I'm new on Qlikview, so I trying to help you and this way helping me to learn more...

CONDITIONAL SUM

Wed, 26 Jan 2011 17:30:29 GMT

Sorry but I can´t upload a qv files. I´ll give you my example and my explanation in two docs

CONDITIONAL SUM

Wed, 26 Jan 2011 17:34:08 GMT

And data to build the file. I´ll give the script in next post

This is the first time I link tables... but second liknig level is too hard for me.

I appreciate any answer.

CONDITIONAL SUM

Wed, 26 Jan 2011 17:36:42 GMT

The script

CONDITIONAL SUM

Wed, 26 Jan 2011 18:09:47 GMT

I found an idea to get my numbrs but it´s not very intuitive to users. To create another table with an ID, AÑO, MES fields and change relationship. But in my formulas it´s necessary to select filtering field because if not, it doesn´t work.

Any more ideas?

Fiber9906

CONDITIONAL SUM

Wed, 26 Jan 2011 18:44:48 GMT

Hope this helps:

add a new filed :

LOAD AÑO,

MES,

DIARIO,

monthname(makedate(AÑO,MES)) as MESAÑO,

// COMPANY,

// ACCOUNT,

COMPANY&'-'&ACCOUNT ID_COMACC,

COMPANY&'-'&ACCOUNT&'-'&AÑO&'-'&MES AS ID_COMACC2,

DH,

IMPORTE,

IMP_CONTA

FROM (biff, embedded labels, table is CONTA$);

then, change your expressions to:

SUM({$<MESAÑO={'<=$(#=max(MESAÑO))'}, MES=, AÑO=, DIARIO={'0'} >} IMP_ANALITICA)

best regards!

CONDITIONAL SUM

Wed, 26 Jan 2011 18:59:06 GMT

It´s a very good reply!! It looks like that it works perfectly!!! I´ve been doing test since yesterday morning.... so you don´t know how appreciate your answer.

Really thank you!

Fiber9906

PS. Where did you know to do that? I don´t find these things on tutorials... (or unlles I don´t know to search for)