topic Set analysis with date in QlikView

Set analysis with date

Fri, 15 May 2015 15:07:22 GMT

Hi everybody

i did this to compare the selections between the selected year and the selected year -1

=Sum({$<YEAR={$(=Only(YEAR))}>} MONEY))

=Sum({$<YEAR={$(=Only(YEAR)-1)}>} MONEY))

and it's working ok

But now i have to do the sam expression BUT i have to see the expression from the selected year/month - 1 year

I mean if i select for example 2015 - april i have to see the graphic from 2014 april to 2015 april (in the two expressions) in the other i have to see from 2013 april to 2014 april

Thank you all

Greetings.

Re: Set analysis with date

Fri, 15 May 2015 15:23:07 GMT

Hi,

you have to create a numeric field (ex PeriodNum) in load script like this:

ex:

Jan-2013 -> 01

Feb-2013 -> 02

Mar-2013 -> 03

Apr-2013 -> 04

...

and then use this new field in set analysis:

=Sum({$<PeriodNum={"<=$(=(Only(PeriodNum))) >=$(=Only(PeriodNum)-12)"}>} MONEY))

=Sum({$<PeriodNum={"<=$(=(Only(PeriodNum)-12)) >=$(=Only(PeriodNum)-24)"}>} MONEY))

Re: Set analysis with date

ramoncova06 — Fri, 15 May 2015 15:33:58 GMT

I agree with Mario, you have to make sure you date format is something that is taken as a number/date

I personally prefer to use the starting date of the month instead of using only the sequential number, that makes it a little easier to work with on the long run

date(makedate(year,month),'MMM-YYYY') as MonthYear

Re: Set analysis with date

Fri, 15 May 2015 16:12:13 GMT

I'd like to not touch the script. The app is very big and it takes 30 minutes to reload it.

I already have a field that is year*12 + month.i could use that. they are numbers.

Re: Set analysis with date

Fri, 15 May 2015 16:35:47 GMT

isnt there a date function to do this?

Re: Set analysis with date

Sun, 17 May 2015 21:03:08 GMT

try with addmonths() function.

ex.

addmonths(YourDataField,12)

addmonths(YourDataField,-12)