Sum, Aggr: Accumulate a value per month

Sat, 29 Nov 2014 11:19:17 GMT

hello,
I have a problem and I was wondering if you could help me please.

I would create two field Key1, Key2

The first Turnover_Janvier do I recover the Turnover January and concatenates with a string [TextConstant].

The Turnover_February accumulated Turnover_Janvier + Net Month February case.

The Turnover_Janvier, Turnover_February and must be divided by 1000 and rounded to two digits.

I did this:
NoConcatenate
MSPCHA_S1:
LOAD *,
IF ([Month] = 'January' Round ([Turnover excluding VAT in Euro] /1000,0.01), [Turnover excluding VAT in Euro]) as [Turnover_Janvier Euro in January],
[TextConstant] & (IF ([Month] = 'January', IF ([Month] = 'January' Round ([Turnover excluding VAT in Euro] /1000,0.01), [Turnover excluding VAT in Euro]) & ';' as [KEY 1],
IF ([Month] = 'February', Round ([Turnover excluding VAT in Euro]/1000,0.01) + (IF ([Month] = 'January' Round ([Turnover excluding VAT in Euro] /1000,0.01), [Turnover excluding VAT in Euro])) as [AC Euro February],

[TextConstant] & IF ([Month] = 'February', Round ([Turnover excluding VAT in Euro]/1000,0.01) + (IF ([Month] = 'January' Round ([Turnover excluding VAT in Euro] /1000,0.01), [Turnover excluding VAT in Euro])) & ';' as [KEY 2]

Resident Extraction_Bil;

it does not work, I have value [Turnover_January Euro] which is the same as that of [Turnover_February Euro ], but is FALSE, will CA_February I do not know how.

In my table I have [Turnover_in Euro] field, MONTHS field, Year field.

Regards

Re: Sum, Aggr: Accumulate a value per month

Anonymous — Sat, 29 Nov 2014 14:13:37 GMT

Hi,

I think this is due to your else statement in the if clause which is for both expressions the same ([Turnover excluding VAT in Euro]). So the if condition doesn't match and then your else condition is executed. This is probably the reason why you get the same values for both fields.

Why don't you try doing this accumulation automatically?:

load*,

if( match(Month ,Previous(Month)) , numsum( TurnoverTmp , peek( ' TurnoverCumulated ' )), TurnoverTmp ) as TurnoverCumulated

;

load*,

Round ([Turnover excluding VAT in Euro]/1000,0.01) as TurnoverTmp

Resident Extraction_Bil

order by Month asc;

hope this helps

Best regards

Stefan

topic Sum, Aggr: Accumulate a value per month in QlikView

Sum, Aggr: Accumulate a value per month

Re: Sum, Aggr: Accumulate a value per month