topic Re: Yearly & Monthly Comparison in QlikView

Yearly & Monthly Comparison

nwilliams — Wed, 08 Jul 2015 09:13:17 GMT

Hi,

I'm trying to compare financial figures from this year against the previous year, both yearly figures, and in month figures.

Ie if I select May 2015, in one column I want to see the figures for May 2015, and in the other column, I want to see May 2014.

I'm using the following formular to try and go back 12 months, but regardless of if the number is "-1" or "-12" it only goes back one month.

=Date(MonthName(Calendar.MonthName-12))

Any ideas?

I also the need to do YTD. I think this is easy(ish) as already have my FY years setup so can do a bit of set analysis to sum in the current FY.

Re: Yearly & Monthly Comparison

Anonymous — Wed, 08 Jul 2015 09:16:13 GMT

you can use your datefield and the function addmonths and Format to you you want to see

=date(addmonths(yourdatefield,-12),'MMM')

Re: Yearly & Monthly Comparison

nwilliams — Wed, 08 Jul 2015 09:25:12 GMT

So if I put that into set analysis, I get an error.

=sum({<[Calendar.MonthName]= {'$(=MonthName(date(addmonths(Calendar.MonthName,-12)))'}>}Finacial_Data_Act.Local_Cost)

Re: Yearly & Monthly Comparison

sasiparupudi1 — Wed, 08 Jul 2015 09:27:53 GMT

what are the values in your MonthName field

Re: Yearly & Monthly Comparison

jonathandienst — Wed, 08 Jul 2015 09:27:57 GMT

What is the format of Calendar.MonthName? The field name implies a string and it will unable to perform date arithmetic. Do you have a normal numeric date field?

Re: Yearly & Monthly Comparison

nwilliams — Wed, 08 Jul 2015 09:32:09 GMT

I'm using the "MonthName" function that is built in. This displays it in a list box as "May 2015".

So my script that creates my calendar table is simply MonthName(c_Date) AS Calendar.MonthName,

Re: Yearly & Monthly Comparison

Michiel_QV_Fan — Wed, 08 Jul 2015 09:36:21 GMT

Neil, add the following to your master calendar:

I'm assuming you have a field in your calendar that is Year + Month (= 2015 jan, 2015 feb, ect.)

autogenerate(Year + Month) as Period_ID. This will generate a list from 1 to ?? for each found value Year + Month.

Then in your set analysis add this:

sum({<Period_ID = {"=$(=max(Period_ID))"}>}Finacial_Data_Act.Local_Cost) for the month of current year

and

sum({<Period_ID = {"=$(=max(Period_ID)-12)"}, Year>}Finacial_Data_Act.Local_Cost) for the month of previous year

and

sum({<Period_ID = {"<=$(=max(Period_ID))"}>}Finacial_Data_Act.Local_Cost) for the month to date of current year

and

sum({<Period_ID = {"<=$(=max(Period_ID)-12)"}, Year>}Finacial_Data_Act.Local_Cost) for the month to date of previous year

the ,Year in in the set analysis will make sure that a year selection is ignored. This is needed because else only the current year is shown.

Re: Yearly & Monthly Comparison

sasiparupudi1 — Wed, 08 Jul 2015 09:57:39 GMT

MonthName(addmonths(Date#(Calendar.MonthName,'MMM YYYY'),-12)) should give you previous year month name.

Re: Yearly & Monthly Comparison

sasiparupudi1 — Wed, 08 Jul 2015 10:08:14 GMT

sum({<[Calendar.MonthName]= {'$(=MonthName(addmonths(Date#([Calendar.MonthName],'MMM YYYY'),-2)))'}>}Finacial_Data_Act.Local_Cost)

Re: Yearly & Monthly Comparison

Kushal_Chawda — Wed, 08 Jul 2015 10:39:04 GMT

Try,

sum({<[Calendar.MonthName]= {'$(=monthname(addmonths(max(Calendar.MonthName),-12)))'}, Year=, Month=, Quarter =>}Finacial_Data_Act.Local_Cost)