topic Re: variable to calculate Total in QlikView

variable to calculate Total

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have columns for each month starting from Jan to Dec as below

JanAmt

FebAmt

MarAmt

...

DecAmt

I need to create a new column in the load script with condition

If we are in May, the TotalAmt column should be JuneAmt + JulyAmt +....DecAmt

If we are in June, the TotalAmt column should change to JulyAmt + AugAmt+...DecAmt

I need this in the load script.

Re: variable to calculate Total

miguelbraga — Wed, 25 May 2016 15:26:37 GMT

Take a special look at my script in my QVW sample:

Hope it helps you

Regards,

Re: variable to calculate Total

Anonymous — Wed, 25 May 2016 15:35:23 GMT

Sorry MB.

You misunderstood the whole question. The solution that you provided has nothing to do with my question.

I have JanAmt, FebAmt...DecAmt as my columns, using which, I need to calculate a new column 'TotalAmt' based on the given conditions. Your example simply pulls data from the excel based on Month condition, nothing else.

Re: variable to calculate Total

Anonymous — Wed, 25 May 2016 15:46:11 GMT

Should i Assume janamt,febamit............in 'Description' as string values ?

should I insert some numbers in 'Descriptopm' ?

if it is numbers you can go with

rangesume(above(.....)

Re: variable to calculate Total

miguelbraga — Wed, 25 May 2016 15:46:16 GMT

Take a look at this:

Regards,

Re: variable to calculate Total

Gysbert_Wassenaar — Wed, 25 May 2016 15:50:54 GMT

Perhaps like this:

LOAD

SomeKindOfMonthField,

JanAmt,

FebAmt,

MarAmt,

....

DecAmt,

Pick(SomeKindOfMonthField, 'January','February', ...etc..., 'October', 'November'),

Rangesum(FebAmt, MarAmt, ...etc..., DecAmnt),

Rangesum(MarAmt, ...etc..., DecAmnt),

....etc....

Rangesum(NovAmt, DecAmnt),

DecAmt

) as SomeAmount

FROM

...

;

Re: variable to calculate Total

Anonymous — Wed, 25 May 2016 15:57:49 GMT

Look at the file. That's what I want. You are taking the column names as values (as rows) which is not at all correct.

In the script, I need to add all the months and create a new column called 'TotalAmt' based on the below condition.

if the current month is May, the TotalAmt column should be calculated from JunAmt :

JunAmt + JulAmt + ...DecAmt

Next month, the TotalAmt should be JulAmt + AugAmt + SepAmt + ..DecAmt

We should calculate the TotalAmt column by adding remaining months excluding current month.

Re: variable to calculate Total

miguelbraga — Wed, 25 May 2016 16:25:43 GMT

Check this solution:

Regards,

Re: variable to calculate Total

Anonymous — Wed, 25 May 2016 16:47:13 GMT

Still quite doesn't give the solution I needed, MB.

JanAmt

FebAmt

MarAmt

AprAmt

MayAmt

JunAmt

JulAmt

AugAmt

SepAmt

OctAmt

NovAmt

DecAmt

TotalAmt

Since we are in May, the TotalAmt column should only add from JuneAmt, JulAmt..until DecAmt

Next month, when the month becomes June, the TotalAmt should calculate from JulAmt, AugAmt...DecAmt

Re: variable to calculate Total

Anonymous — Wed, 25 May 2016 18:09:39 GMT

The Source Excel MB showed is not the right data structure. Please see my Sample Excel