topic Re: Calculate Previous Month Value based on Filter Selection in QlikView

Calculate Previous Month Value based on Filter Selection

RohanPR — Tue, 26 Feb 2019 12:29:07 GMT

Hi,

I know this topic has been discussed many time and I have gone through each and every post. However, I am unable to get previous month value (count of account number) for the current year in my data set. The sample of my data set is given below

Month_Production , Acc_No, Category

Jan -18 , 123456,E

Jan-18, 234567,E

Jan-18, 123567,E

Feb-18, 246801, E

Feb-18, 357680,E

Mar-18,786502,E

Jan-18, 234590, P

Jan-18, 349086,P

Jan-18, 124509,P

Feb-18, 567890,P

Feb-18, 788090,P

Feb-18, 890877,P

E.g. 1 If I select category E and Month_Prod as Feb-18 from the multi drop down filter then the Prev month count for account number should display 3

E.g. 2 If I select category E and Month_Prod as Mar-18 from the multi drop down filter then the Prev month count for account number should display 2.

PS: I wan to display the number in the text box in front end. I used set analysis but didn't end up with right answer. Looking forward to a quick TAT. Thanks in advance

Thanks and Regards,

Rohan

Re: Calculate Previous Month Value based on Filter Selection

sunny_talwar — Wed, 27 Feb 2019 02:17:35 GMT

First thing first, how exactly do you create Month_Production field in the script? Is it created using Date() function?

Re: Calculate Previous Month Value based on Filter Selection

RohanPR — Wed, 27 Feb 2019 04:25:04 GMT

I am not creating the month_production field in my script. It is coming from the raw file. It is not created using date function.

It indicates the first day of the month. e..g 1/1/2018, 2/1/2018,3/1/2018 etc. It is in 'MM/DD/YYYY' format and is represented as Jan-18, Feb -18 etc

Re: Calculate Previous Month Value based on Filter Selection

sunny_talwar — Wed, 27 Feb 2019 12:01:52 GMT

In that case, it might not be read as a date field, but in order to make it work, get it to be read as a date field.

Date(MonthStart(Date#(Month_Production, 'MMM-YY')), 'MMM-YY') as Month_Production

now use this for your last month's expression

Count({<Month_Production = {"$(=Date(MonthStart(Max(Month_Production), -1), 'MMM-YY'))"}>} Acc_No)

Re: Calculate Previous Month Value based on Filter Selection

RohanPR — Wed, 27 Feb 2019 16:12:32 GMT

OK thanks for the reply let me try it and get back to you on the same

Re: Calculate Previous Month Value based on Filter Selection

RohanPR — Thu, 28 Feb 2019 06:12:28 GMT

Hi,

I tried the calculation you have me. However, the first calculation evaluates to blank. Any idea why this is happening?

Regards,

Rohan

Re: Calculate Previous Month Value based on Filter Selection

RohanPR — Thu, 28 Feb 2019 07:17:31 GMT

In the inline load statement i tried and it worked. However, in my data set it didn't work. When I dug deep I found out that the month_production is given as 1/1/2018,2/1/2018,3/1/2018 etc in MM/DD/YYYY format. So when I tried applying your formula it failed and gave me a blank value. I removed the date function you had used and applied the formula. Is there any other way I can calculate or should I convert it into Jan-18,Feb-18 etc and then apply your formula

Regards,

Rohan

Re: Calculate Previous Month Value based on Filter Selection

sunny_talwar — Thu, 28 Feb 2019 12:06:00 GMT

@RohanPR wrote:
Hi,
I tried the calculation you have me. However, the first calculation evaluates to blank. Any idea why this is happening?

First calculation? Which 1st calculation are you talking about?

Re: Calculate Previous Month Value based on Filter Selection

RohanPR — Thu, 28 Feb 2019 12:38:22 GMT

Date(MonthStart(Date#(Month_Production, 'MMM-YY')), 'MMM-YY') as Month_Production

This is the first calculation I was talking about. However, I kind of tweaked your date format to suit my data and it is working now. Thanks a lot. Appreciate it 🙂

Count({<Month_Production = {"$(=Date(MonthStart(Max(Month_Production), -1), 'MMM YY'))"}>} Acc_No)

This is the final formula i used finally

Regards,

Rohan

Re: Calculate Previous Month Value based on Filter Selection

sunny_talwar — Thu, 28 Feb 2019 12:50:19 GMT

Awesome, I am glad you were able to figure it out.

Re: Calculate Previous Month Value based on Filter Selection

RohanPR — Thu, 28 Feb 2019 12:56:36 GMT

again thanks for all your help

Re: Calculate Previous Month Value based on Filter Selection

RohanPR — Fri, 08 Mar 2019 06:02:08 GMT

Hi Sunny,

I am reopening this discussion again. I am facing a peculiar problem. When I do the calculation you provided in a text box I get a different number for both current month as well as for previous month. However, when I plot it on a graph either as a table or bar chart I get the exact correct numbers which is there in my excel. Jut to give some more context, the account numbers repeat in a month. Any reason why this is happening? My account number gets linked with other tables. However, that is not causing the problem since the table chart gives me exact numbers but in the text box I get a different number..

I need count(Account Number) every month and not count(distinct Account Number) in the text box. I tried count(Account Number) in the text box calculation, however it doesn't count duplicate values

Regards,

Rohan

Re: Calculate Previous Month Value based on Filter Selection

sunny_talwar — Fri, 08 Mar 2019 11:55:17 GMT

Would you be able to share a sample where we can see the issue?

Re: Calculate Previous Month Value based on Filter Selection

dching — Fri, 30 Sep 2022 16:32:34 GMT

Hi Guys - i am new to qlik sense - i have the same issue - i want previous month KPI to be based on filtered selection. e.g. if i select June i want previous month(May)figures to show - can someone please help ??