topic Re: problem in set analysis in QlikView

problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:03:16 GMT

i used this set analysis

TBAL_CAL_MONTH={$(=max(TBAL_CAL_MONTH)-6)}

its giving last six month value. ex if i select 12th month its showing 6th month value. but if i select 5th month it showing blank values

it should show last year 12th month value.

why its not showing please clarify anyone?

thanks

Re: problem in set analysis

PrashantSangle — Tue, 24 Mar 2015 11:08:46 GMT

Hi,

debug your expression in text object

just write

=max(TBAL_CAL_MONTH)-6

in text object

and then select value from TBAL_CAL_MONTH field

Regards

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:11:20 GMT

ya i did that too. up to 6th month its showing correct. if i select it showing -1. i dont know why

please help

thanks

Re: problem in set analysis

peterwh — Tue, 24 Mar 2015 11:18:36 GMT

Hello,

it's simple: If your number of the month is 5 and you substract 6, you get -1, because the month is simply a number. If you select 3 and substract 6, you get -3. To get what you want you have to use a date-field and the AddMonths(startdate, n)-function. If you set n = -6 you get a date which is 6 months earlier than the startdate.

Kind regards

Peter

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:18:42 GMT

any help guys?

Re: problem in set analysis

PrashantSangle — Tue, 24 Mar 2015 11:20:22 GMT

Hi,

Are your creating TBAL_CAL_MONTH from any Date Field??

When you subtracting 6 from 5 then you will get -1 not 12 because Qlikview dont understand that you are subtracting month it is consider as subtracting a number from another number.

Hope you get reson for getting -1.

Try

if you are creating month number field from any Date Field then use

AddMonths()

like AddMonths(max(DateField),-6)

then you will get expected result.

Regards

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:23:20 GMT

HI Peter,

Like this

=date(AddMonths(TBAL_CAL_MONTH,-6),'MMM')

result for this showing jul only not changing can you tell me how to do it?

thanks

Re: problem in set analysis

stevedark — Tue, 24 Mar 2015 11:23:27 GMT

You need to deal with when the month goes into the prior year, eg:

TBAL_CAL_MONTH={$(=max(TBAL_CAL_MONTH)+if(max(TBAL_CAL_MONTH) <= 7, 6, -6))}

And also take year into account:

TBAL_CAL_YEAR={$(=max(TBAL_CAL_YEAR)+if(max(TBAL_CAL_MONTH) <= 7, -1, 0))}

The code may not be exact, as I can't test it, but hopefully you get the idea

Steve

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:26:37 GMT

hi Max,

i dont have any date field i have only month and year

Re: problem in set analysis

peterwh — Tue, 24 Mar 2015 11:27:08 GMT

No,

it's wrong again. You can't only work with a Month-Field. A month is only a value between 1 and 12, but you need a complete date: 2015-03-25.

Like this:

=date(AddMonths(TBAL_CAL_DATE,-6),'MMM')

with TBAL_CAL_DATE as datefield.

I've seen your other answer,

so you can do this

=date(AddMonths(makedate(TBAL_CAL_Year, TBAL_CAL_MONTH),-6),'MMM')

With makedate(TBAL_CAL_Year, TBAL_CAL_MONTH) you create a date like 2015-03-01, if you only have Year and Month)

Kind regards

Peter

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:28:55 GMT

Hi Steve,

I used this in set analysis but still getting blank values only. am ding any mistake?

thanks

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:29:30 GMT

i dont have complete date field

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:32:25 GMT

Hi peter

should i use this in set above analysis

Re: problem in set analysis

PrashantSangle — Tue, 24 Mar 2015 11:37:09 GMT

Then Create DateField at script level.

Use MakeDate()

MakeDate(Year,Month)

Regards

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:39:00 GMT

Hi all

please look at this

MakeDate(TBAL_CAL_YEAR,TBAL_CAL_MONTH) as datefield

vPrior6=date(AddMonths(datefield,-6),'MMM')

in expression

sum({<datefield={'>=$(vPrior6)'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

the result is getting current value only not previous six month value

please check it if you want sample i can do that too

thanks

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:41:11 GMT

hi max,

check below what i did if any mistake please tell me

Re: problem in set analysis

PrashantSangle — Tue, 24 Mar 2015 11:46:31 GMT

Hi,

try like

Sum({datefield={">=$(=date(Addmonths(max(datefield),-6)))<=$(=date(max(datefield)))"}}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

Check if i missed any brackets and also check date format of datefield and after

date(max(datefield))

Regards

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:48:01 GMT

here is the sample app

Re: problem in set analysis

Tue, 24 Mar 2015 11:48:38 GMT

Hi Arul,

Can you share your Qlikview file with sample data so that we can try it.

Regards

Priyanka

Re: problem in set analysis

arulsettu — Tue, 24 Mar 2015 11:53:40 GMT

hi max,

getting same current value only datefield format is M/D/YYYY