topic Re: Last 3 months in Set Analysis in QlikView

Last 3 months in Set Analysis

Tue, 03 May 2016 08:52:29 GMT

Good day

I have the following calculations on my set analysis:

sum({<L_WRITTEN_OFF_L3M_IND={'Y'}, L_CREDIT_DEBIT_IND={'DB'},L_NCA_PRODUCT_CODE={'UNL'}, L_NCA_IND={'J','Y','X'}>}[L WRITTEN OFF AMOUNT])

Now I want to use this set to calculate for the last three months.

Your assistance will be highly appreciated.

Kind regards,

Mbini

Re: Last 3 months in Set Analysis

mightyqlikers — Tue, 03 May 2016 08:55:20 GMT

create a variable like vMonStart = monthstart(addmonths(datefield,-3))

vMonEnd = dateField

sum({<L_WRITTEN_OFF_L3M_IND={'Y'}, L_CREDIT_DEBIT_IND={'DB'},L_NCA_PRODUCT_CODE={'UNL'}, L_NCA_IND={'J','Y','X'},DateFeild ={'>=$(vMonStart ) <=$(vMonEnd )'}>}[L WRITTEN OFF AMOUNT])

Regards

$@M.

Re: Last 3 months in Set Analysis

Kushal_Chawda — Tue, 03 May 2016 08:59:38 GMT

What is this Flag L_WRITTEN_OFF_L3M_IND?

Re: Last 3 months in Set Analysis

Tue, 03 May 2016 09:01:46 GMT

yes its a flag

Re: Last 3 months in Set Analysis

Kushal_Chawda — Tue, 03 May 2016 09:05:03 GMT

So is this Last 3 months Flag?

Re: Last 3 months in Set Analysis

Tue, 03 May 2016 09:36:22 GMT

its a flag that checks if its been written off in the last three months

Re: Last 3 months in Set Analysis

Tue, 03 May 2016 09:37:50 GMT

my datefiled is 'Date'

Re: Last 3 months in Set Analysis

jonathandienst — Tue, 03 May 2016 09:46:09 GMT

Define the variables: ( I always do this in the load, but you can use the variable overview as well):

Set vPeriodStart = '=AddMonths(Max(Date))';

Set vPeriodStart = '=AddMonths(Max(Date), -3)';

Set vPeriodEnd = '=Date(Max(Date))';

Then use the expression:

sum({<

L_WRITTEN_OFF_L3M_IND={'Y'},

L_CREDIT_DEBIT_IND={'DB'},

L_NCA_PRODUCT_CODE={'UNL'},

L_NCA_IND={'J','Y','X'},

Date ={">=$(vPeriodStart) <=$(vPeriodEnd)'}>} [L WRITTEN OFF AMOUNT]

)

Re: Last 3 months in Set Analysis

Tue, 03 May 2016 10:12:21 GMT

Try this

sum({<L_WRITTEN_OFF_L3M_IND={'Y'}, L_CREDIT_DEBIT_IND={'DB'},L_NCA_PRODUCT_CODE={'UNL'},L_NCA_IND={'J','Y','X'},DATEFIELD={">=$(=MonthStart(max(DATEFIELD),-3))<=$(=max(DATEFIELD))"}>}[L WRITTEN OFF AMOUNT])

Re: Last 3 months in Set Analysis

Tue, 03 May 2016 11:49:36 GMT

I have tried this, I get a negative value

Re: Last 3 months in Set Analysis

Kushal_Chawda — Wed, 04 May 2016 11:00:41 GMT

Make sure that Your Date field is not the string, to check this create the Text object and put the expression "=max(Date)", if you get '-' means it is not in Date format, if you get the Values , it means it is in Date format.

If it is not in Date format, use Date# function to make it in Date format, then use below expression

sum({<

L_WRITTEN_OFF_L3M_IND={'Y'},

L_CREDIT_DEBIT_IND={'DB'},

L_NCA_PRODUCT_CODE={'UNL'},

L_NCA_IND={'J','Y','X'},

Date ={">=$(=addmonths(max(Date),-3)) <=$(=max(Date))"}>} [L WRITTEN OFF AMOUNT])

Last 3 months in Set Analysis