Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Sum, Aggr: Accumulate a value per month

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

Tags (3)
1 Reply
s_kunte23
Contributor III

Re: Sum, Aggr: Accumulate a value per month

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

Community Browser