topic Re: How to get the previous month count in QlikView

How to get the previous month count

Wed, 25 Nov 2020 16:16:04 GMT

How to get the previous month count, as per attached example need the previous month (Mar’15) count in current month (Apr’16) for the same department

Please Help. Thanks in advance

Re: How to get the previous month count

sunny_talwar — Thu, 09 Jun 2016 12:17:20 GMT

May be used Above() function or use As-OfTable in the script: The As-Of Table

Re: How to get the previous month count

ramasaisaksoft — Thu, 09 Jun 2016 12:23:00 GMT

Hi Ashok,

could you please check for Accumulation is Same?

else you need follow Sunny advice.

Re: How to get the previous month count

Thu, 09 Jun 2016 12:23:20 GMT

Hi Sunny. Thanks for your quick reply.

I tried to using above function but for the first month my results shows 0. Please advice as i am new to qlikview.

Re: How to get the previous month count

Digvijay_Singh — Thu, 09 Jun 2016 12:25:52 GMT

Can you share formula used for May month, as it is showing previous month count, March month might not be under current selection values so it should read beyond current selection through different ways as suggested above.

Re: How to get the previous month count

Thu, 09 Jun 2016 12:33:53 GMT

Please see the used below expression for previous count

if(Month='Apr',

sum(aggr(count({$<Active_Flag = {1},Cal_Year = {'$(vPrev_Year)'},Cal_Year_Flag = {1},Cal_Month =>}Distinct [Employee Code]),Department)),

sum(aggr(above(Count({<Cal_Month_Flag = {1},Cal_Year = {'$(vMax_Year)'},Active_Flag= {1}>}Distinct [Employee Code])),Department,Month)))

Re: How to get the previous month count

sunny_talwar — Thu, 09 Jun 2016 13:29:25 GMT

Would you be able to share the application you are working with?

Re: How to get the previous month count

Fri, 10 Jun 2016 05:04:54 GMT

Please find the attached working application. Please check and advise. !

Re: How to get the previous month count

pho3nix90 — Fri, 10 Jun 2016 08:30:51 GMT

Give this a shot

Re: How to get the previous month count

sunny_talwar — Fri, 10 Jun 2016 10:16:24 GMT

This?

Expression:

If([Current Active] > 0,

RangeSum(above(Count({<Cal_Month_Flag = {1},Cal_Year = {'$(vMax_Year)'},[Active Flag DayWise] = {1}>}Distinct [Employee Code])),Department,Cal_Month))

Re: How to get the previous month count

Fri, 10 Jun 2016 10:25:10 GMT

I am still not able to get correct values. Need Mar'15 values in Apr'16 for the specific department.

Please help. Thanks for your effort and time.

Re: How to get the previous month count

sunny_talwar — Fri, 10 Jun 2016 14:47:40 GMT

Last years previous values?

Re: How to get the previous month count

Tue, 14 Jun 2016 06:38:52 GMT

Hi Sunny,

Absolutely right. Please help with the same.Thanks

Re: How to get the previous month count

pho3nix90 — Tue, 14 Jun 2016 09:34:47 GMT

The solutions thus far is to use inter record functions within the chart, it seems you are using a financial calendar and thus year starts on April, to show the previous value for April you will need to display March in the table as well for the current solutions.

What you need is for the calculations to be done in script. Please upload the "Tes.xlsx" so that I can give you another example.

Re: How to get the previous month count

Tue, 14 Jun 2016 09:43:21 GMT

Hi Shaun,

You are right. Please find the attached excel data for your reference.

Thanks for your support.

Re: How to get the previous month count

pho3nix90 — Tue, 14 Jun 2016 13:37:41 GMT

Please see attached