topic Re: Comparative Analysis with (previous month data) in QlikView

Comparative Analysis with (previous month data)

suresh_rawat — Thu, 17 Jan 2013 09:08:07 GMT

Hi All,

I want to creat a report on comparison of : -

1)current year month(2013-Jan) - sum( {$<year = {$(=Max(year))}>} Wholesale.TOTAL_QTY)

2) previous year month(2012-Jan) - sum( {$<year = {$(=Max(year)-1)}>} Wholesale.TOTAL_QTY)

3) current year month -1 (2012-Dec),

I done with first 2 points by set analysis with this formula but what set analysis i hv to use to create a graph for 3 point.

Please refer the below image for reference, i have to create such chart in QV.

If possible reply soon, its urgent.

Thanks & Regards

Suresh

Re: Comparative Analysis with (previous month data)

nirav_bhimani — Thu, 17 Jan 2013 09:29:06 GMT

Try this

Last year Month Sales: (previous year month)

=Sum({<CalendarYear=, MonthName=,OrdDate={'>=$(=MonthStart(Max(OrdDate), -12))<=$(=MonthEnd(Max(OrdDate), -12))'}>} Total_Qty)

For Current Month Sales:

=sum({<CalendarYear={$(=max(CalendarYear))},MonthName={$(=Month(Max(OrdDate)))}>} Total_Qty)

For Last Month Sales:

=sum({<CalendarYear={$(=max(CalendarYear))},MonthName={$(=month(addmonths((max(OrdDate)),-1)))}>}Total_Qty)

Regards,

Nirav Bhimani

Re: Comparative Analysis with (previous month data)

Hafedh_BOUMESSOUER — Thu, 17 Jan 2013 09:30:23 GMT

HI ,

For the third:

i think that you need to do for srt_analyses :

{$<year = {$(=Max(year)), Month=(Month(todat()-1))}>}

Re: Comparative Analysis with (previous month data)

suresh_rawat — Thu, 17 Jan 2013 10:13:36 GMT

Hi Nirav Bhimani,

Can you share me the QV application where you used these expressions??

Re: Comparative Analysis with (previous month data)

nirav_bhimani — Thu, 17 Jan 2013 11:48:53 GMT

HI,

PFA

Hope this will help you.

Regards,

Nirav Bhimani

Re: Comparative Analysis with (previous month data)

prasad_dumbre — Thu, 17 Jan 2013 12:08:12 GMT

1) For Current Year, Current Month:

=sum({<CalendarYear={$(=max(CalendarYear))},CalendarMonth={$(=Month(Max(Date)))}>} ReceiptAmount)

2) For Same Month of Previous Year:

=sum({<CalendarYear={$(=max(CalendarYear)-1)}>},MonthNumber={"<=$(=max({<Year={$(=max(Year))}>} MonthNumber))"}>}} ReceiptAmount)

3) For Prev. Month:

sum({<CalendarYear={$(=max(CalendarYear))},CalendarMonth={$(=Month(Max(Date)-31))}>} ReceiptAmount)

Hope this works for you . . .