topic Re: Current vs. Previous Month in QlikView

Current vs. Previous Month

Tue, 09 Jul 2013 18:49:01 GMT

I am trying to calculate Previous month invoice amount compared to current month invoice amount.

I am using SUM(INVC_ITEM_AMT) to get my current month invoice charges

How do I get my Previous month invoice charges?

Thank you,

dawn

Re: Current vs. Previous Month

nirav_bhimani — Tue, 09 Jul 2013 19:14:58 GMT

Hi,

Used the following expression

For Current Month Sales:

=sum({<CalendarYear={$(=max(CalendarYear))},CalendarMonthName={$(=Month(Max(InvDate)))}>} INVC_ITEM_AMT)

For Last Month Sales:

=sum({<CalendarYear={$(=max(CalendarYear))},CalendarMonthName={$(=month(addmonths((max(InvDate)),-1)))}>}INVC_ITEM_AMT)

Regards,

Nirav Bhimani

Re: Current vs. Previous Month

Tue, 09 Jul 2013 20:15:57 GMT

Thanks for your reply.

I used the calculations above and got Current Month Information for both columns. Is there something else I need to do in order for it to calculate properly?

dawn

Re: Current vs. Previous Month

Sokkorn — Wed, 10 Jul 2013 04:15:56 GMT

Hi Dawn,

I would suggest to create a new field (bold) for easy control in set analysis, something like

[Table]:

LOAD

...

[InvoiceDate],

[INVC_ITEM_AMT],

Year([InvoiceDate])*12 + Month([InvoiceDate]) AS [%YM_Seq],

...

Then in expression, we can try

For current month invoice amount

=Sum({$<[%YM_Seq] = {$(=Max([%YM_Seq]))}>} [INVC_ITEM_AMT])

For previous month invoice amount

=Sum({$<[%YM_Seq] = {$(=Max([%YM_Seq])-1)}>} [INVC_ITEM_AMT])

For last three month invoice amount

=Sum({$<[%YM_Seq] = {$(=Max([%YM_Seq])-3)}>} [INVC_ITEM_AMT])

Regards,

Sokkorn

Re: Current vs. Previous Month

narender123 — Wed, 10 Jul 2013 05:18:14 GMT

Hi,

Try this.

for Current Month

=SUM({<Year={$(=Max(Year))},Month={$(=MaxString(Month))}>}INVC_ITEM_AMT)

=SUM({<Year={$(=Max(Year))},Month={$(=MonthString(Today()))}>}INVC_ITEM_AMT)

for Previous Month

=SUM({<Year={$(=Max(Year))},Month={$(=Max(Month)-1)}>}INVC_ITEM_AMT)

=SUM({<Year={$(=Max(Year))},Month={$(=num(month(Today())-1))}>}INVC_ITEM_AMT)

Thanks.