topic Re: Current Month v. Same Month Last Year in QlikView

Current Month v. Same Month Last Year

userid128223 — Mon, 06 May 2013 16:55:28 GMT

Hi Everyone

I have ticketing application which logs number of calls per month. I have data for 5 years. I want to calculate what variance between calls during same period last year around same month to guage the trend.

How do I go about doing this.

please help.

thanks

Re: Current Month v. Same Month Last Year

vivek_niti — Mon, 06 May 2013 17:09:11 GMT

Hi,

Pass the current monthyear to a variable in form of a date. Create a second variable using the first variable with addmonths function.

eg:

vCurrDate = Max(Date);

vCurrMonthStart = Date(Floor(Monthstart(max(Date))),'DD-MM-YYYY')

vPrevCurrDate = Date(Addmonths($(vCurrDate),-12),'DD-MM-YYYY')

vPrevMonthStart = Date(Addmonths($(vCurrMonthStart),-12),'DD-MM-YYYY')

Use these variables to limit the dates in the formula.

Regards,

Re: Current Month v. Same Month Last Year

userid128223 — Mon, 06 May 2013 18:56:52 GMT

Thanks Vivek

Can you please explain what your formula does and how it is tied to us getting current month vs same month last year.

I have create a example app with your suggestion can you please take this and show me how you will use this to show current month vs last year same month data trend.

thanks.

Re: Current Month v. Same Month Last Year

userid128223 — Mon, 06 May 2013 20:15:07 GMT

Any update Vivek or anyone else.

thanks

Re: Current Month v. Same Month Last Year

userid128223 — Mon, 06 May 2013 21:12:17 GMT

vPrevMonthStart = Date(Addmonths($(vCurrMonthStart),-12),'DD-MM-YYYY')

is not giving me the right results eather.

Re: Current Month v. Same Month Last Year

vivek_niti — Tue, 07 May 2013 03:17:49 GMT

Hi,

Am leaving for office...will send u the codes later in the evening.....

Regards,

Vivek

Re: Current Month v. Same Month Last Year

Sokkorn — Tue, 07 May 2013 04:04:50 GMT

Hi amiuni66,

Here is a clue

Sum({$<Years = {$(=Max(Years)-1)}, MonthNum = {$(=Max(MonthNum))}>} NoOfCalls) ==>Return value in previous year

Sum({$<Years = {$(=Max(Years))}, MonthNum = {$(=Max(MonthNum))}>} NoOfCalls) ==>Returen value in current year

See sample attached file also.

Note: I just add one field in load script Num(Month(IncidentDate)) As MonthNum,

Regards,

Sokkorn

Re: Current Month v. Same Month Last Year

userid128223 — Tue, 07 May 2013 04:27:28 GMT

Thanks Vivek. I will look forward to it.

Thanks Sukkorn. I tried your solution it works without any selection but when you select any month-year it does not show proper results for prior year. it shows Zero.

Re: Current Month v. Same Month Last Year

neha_shirsath — Tue, 07 May 2013 05:45:12 GMT

Hi,

Try this for last year month sales-

Sum ({<Date = P({<Year={$(=max(Year)-1)}>}Date)>}Sales)

Regards,

-Neha

Re: Current Month v. Same Month Last Year

kumarnatarajan — Tue, 07 May 2013 06:27:08 GMT

Hi amiumi,

Please check the attached file.This is very useful for you

Re: Current Month v. Same Month Last Year

userid128223 — Tue, 07 May 2013 13:32:52 GMT

Thanks Kumar & Neha.

But your solution does not work. I need to be able to select any point in data and it will show data from same month previous year.

Hi Vivek

Waiting for your reply. I think we were close.

thanks

Re: Current Month v. Same Month Last Year

Gysbert_Wassenaar — Tue, 07 May 2013 18:48:43 GMT

See attached example

edit: see here for an explanation of the approach used.

Re: Current Month v. Same Month Last Year

userid128223 — Tue, 07 May 2013 19:09:55 GMT

Hi Gysbert

Can you please explain how it works.

is it possible for you to do it with variable the way Vivek was doing. He simple did not have time to complete.

My concern is how i can replicate this way in my script with left join as i have multiple tables. Variable options sounds much better.

Re: Current Month v. Same Month Last Year

vivek_niti — Tue, 07 May 2013 19:28:27 GMT

Hi,

please find the attahced file...

Have made some changes in the variables...

Hope it works for you...

Regards,

Vivek

Re: Current Month v. Same Month Last Year

userid128223 — Tue, 07 May 2013 19:42:02 GMT

Thanks Vivek

but it still does not give me right answer. if i select jan 2013, it should show previous year jan 2012 numbers along side, which it does not show.

Re: Current Month v. Same Month Last Year

Tue, 07 May 2013 21:55:52 GMT

you can try this expression in Vivek's example qvw....

=if (len(GetFieldSelections(MonthYear))=0,

sum({<IncidentDate_num={'>=$(vPrevMonthStart)'}, IncidentDate_num={'<=$(vPrevCurrDate)'}>}NoOfCalls),

sum({<MonthYear={'$(=Left(MonthYear,4)&num(right(MonthYear,4)-1))'}>}NoOfCalls))

Re: Current Month v. Same Month Last Year

vivek_niti — Thu, 09 May 2013 02:47:54 GMT

Hi,

the formula works fine...if u select jan 2013 it shows the total value of jan 2012.

sum({<Year=,Month=,MonthYear=,IncidentDate_num={">=$(vPrevMonthStart)<=$(vPrevEndDate)"}>}NoOfCalls)

Regards,

Vivek

Re: Current Month v. Same Month Last Year

userid128223 — Tue, 14 May 2013 20:57:59 GMT

Hi Vivek

its not working for me. Let me ask you question differentely

If i have month, year, date and I have data.

I have columns that shows month sales. How can i shows (via generic formula) same month previous year sales data right next to it.

Re: Current Month v. Same Month Last Year

userid128223 — Thu, 23 May 2013 15:15:27 GMT

Hi Gysbert

Thanks for you help. I am still having hard time understand the concept but your solution does work.