topic IGNORE PREVIOUS MONTH DATA in Qlik Automate

IGNORE PREVIOUS MONTH DATA

inam — Fri, 11 Aug 2023 05:22:56 GMT

Hello Community,

i have a column [Opportunity ID]. with the help of =count ([Opportunity ID] ) i am creating MTD Achieved and i am taking 2 month data. (previous month & current month (july to today-1 ) today is 11 aug so i have data 1ST july to 10 august ) & in my dashboard i am taking last 4 date data see below .

so my question is if today is 2nd august so i want current month MTD Achieved (not july month)

If TODAY DATE IS 2 AUGUST

c product	29-07-2023	30-07-2023	31-07-2023	01-08-2023	MTD Achieved
a	8	6	5	5	Here I want only aug month =count([Opportunity ID] )
b	5	47	36	10	Here I want only aug month =count([Opportunity ID] )
c	9	100	56	6	Here I want only aug month =count([Opportunity ID] )


IF TODAY DATE IS 3 AUGUST

c product	30-07-2023	31-07-2023	01-08-2023	02-08-2023	MTD Achieved
a	6	5	5	25	Here I want only aug month =count([Opportunity ID] )
b	47	36	10	19	Here I want only aug month =count([Opportunity ID] )
c	100	56	6	4	Here I want only aug month =count([Opportunity ID] )

1ST AUG all working fine because i have today-1 data so in 1st august i have data 1st july to 31st july so MTD Achieved count properly only july month. but 2nd august i have data 1st july to 1st august so here is taking 1st july to 1st august MTD but i want only currrent month (ONLY AUG MONTH MTD ).

Re: IGNORE PREVIOUS MONTH DATA

Tanish — Fri, 11 Aug 2023 05:58:22 GMT

Hi Inam,

Try this

Count({<Date = {" >=$(=Monthstart(Max(Date_field))) <= $(=Max(Date_field))"} >}[Opportunity ID] )

Hope this will Help...

Re: IGNORE PREVIOUS MONTH DATA

inam — Fri, 11 Aug 2023 07:44:12 GMT

hello @Tanish Thanks for the reply

i using your code but not getting value. & i am also used below formula but not getting value can you please correct me.

=IF(Day(Today()) = 1,
Num(COUNT({<[Date Created] = {">=$(=Floor(MonthStart(Date(Max(Today() - 1)))))<=$(=Floor(MonthStart(Today() - 1)))"}>} [Opportunity ID])),
Num(COUNT({<[Date Created] = {">=$(=Date(MonthStart(Max(Today())), 'DD-MM-YYYY'))))<=$(=DATE(Today(),'DD-MM-YYYY')"}>} [Opportunity ID])))

Re: IGNORE PREVIOUS MONTH DATA

Tanish — Fri, 11 Aug 2023 08:31:09 GMT

Hi Inam,

1. If you use Today function your Date filter will not work, you need to use Max of your Date field instead of Today. For Example - Max(Date)

2. In this formula

'Num(COUNT({<[Date Created] = {">=$(=Floor(MonthStart(Date(Max(Today() - 1)))))

<=$(=Floor(MonthStart(Today() - 1)))"}>} [Opportunity ID])),

I think these both would be giving same answer and also in the number.

Is your Date fields is also in the num format. If not then it is wrong.

Here is the script we use

Sum({<Date = {">=$(vMTD_start)<=$(vMaxDate)"}>}Amount)

Variable

vMTD_start

=(Monthstart(Max(Date)))

vMaxDate

=Max(Date)

Re: IGNORE PREVIOUS MONTH DATA

inam — Fri, 11 Aug 2023 10:38:02 GMT

which formula should i use ? i want count ([Opportunity ID] ) for current month and for date i have column "Date Created" DD-MM-YYYY.....Please Can you give me proper expression.

Re: IGNORE PREVIOUS MONTH DATA

Tanish — Sun, 13 Aug 2023 12:54:50 GMT

Here is the script we use

Count({<"Date Created" = {">=$(vMTD_start)<=$(vMaxDate)"}>}[Opportunity ID])

Try this out.....

Re: IGNORE PREVIOUS MONTH DATA

inam — Thu, 17 Aug 2023 09:49:20 GMT

hello @Tanish thanks for the help i am using different code for MTD and its working fine(for MTD i am using this code:- =IF(Day(now())=1,
Count([Opportunity ID]),
Count({< [Date Created]= {">=$(=Date(MonthStart(MAX(Today())),'DD-MM-YYYY'))<=$(=Date(Today()-1,'DD-MM-YYYY'))"} >} [Opportunity ID]))) its working fine now

but i have one other doubt.

i am taking 4 expression for calculate last 4 date data. see below code

=if(day(Today())=1,
Num((Count({< Floor_DC = {"=floor(Date(MonthEnd(Now()-1)-3,'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 4)))))) <=$(=(floor(Date(Now()-4))))"}>} PROD_TYPE)),'##'))

=if(day(Today())=1,
Num((Count({< Floor_DC = {"=floor(Date(MonthEnd(Now()-1)-2,'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 3)))))) <=$(=(floor(Date(Now()-3))))"}>} PROD_TYPE)),'##'))

=if(day(today())=1,
Num((Count({< Floor_DC = {"=floor(Date(MonthEnd(Now()-1)-1,'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 2)))))) <=$(=(floor(Date(Now()-2))))"}>} PROD_TYPE)),'##'))

=if(day(today())=1,
Num((Count({< Floor_DC = {"=floor(date(MonthEnd(Now()-1),'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 1)))))) <=$(=(floor(Date(Now()-1))))"}>} PROD_TYPE)),'##'))

and its working fine if the date is 1st august is show last 4 date data.

but i want if the date is 2 august is show only current month date data other colum shows '0' see below example

If TODAY DATE IS 2 AUGUST

c product	29-07-2023	30-07-2023	31-07-2023	01-08-2023
a	0	0	0	5
b	0	0	0	10
c	0	0	0	6