topic Re: data between the last 8 months and 2 months in QlikView

data between the last 8 months and 2 months

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

I'm gathering 6 months of data (not taking last month into consideration but the months previous.

Anyone see any issue below?

= Concat(DISTINCT{< Country ={"=Sum({<[Control Description], Country,

[Fiscal Year Month]= {"=Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') >= AddMonths(Today(), -8)

and Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') <= AddMonths(Today(), -2)"} >}

[Record Count]) & 'is more than 3000'& chr(13))

Re: data between the last 8 months and 2 months

Anil_Babu_Samineni — Mon, 19 Dec 2016 10:37:38 GMT

What is your intend to show, What you want to get in between of these days

I assume, Today is 19-12-2016 and you want to get [Record Count] between these dates >= 19-04-2016 <= 19-10-2016 and then what are you trying. Can you describe more of your exact issue. I mean you want aggregation values or some concat for [Record Count]

Re: data between the last 8 months and 2 months

bobbydave — Mon, 19 Dec 2016 11:39:09 GMT

I'll start from the start.

What I am trying to do is create a condition in an Alert that will

A) sum([Record Count]) for last month

and also to get the

B) sum([Record Count]) for as you said Apr 2016 through to Oct 2016)

I am then going to divide these two numbers and if the Condition is greater than 20%, give me the alert, otherwise all is working fine and i get no alert.

=Concat(

DISTINCT {<Country = {"=Sum( {< Country , [Control Description] = {'Claims Registered'},

[Fiscal Year Month]= {'2016-11'} >} [Record Count])/

Sum({< Country, [Control Description] = {'Claims Registered'},

[Fiscal Year Month]= {"=Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') >= AddMonths(Today(), -8)

and Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') <= AddMonths(Today(), -2)"}>}

[Record Count]), Country) > 0.2"}>}

Country & ' is the issue' & chr(13))

Re: data between the last 8 months and 2 months

PrashantSangle — Mon, 19 Dec 2016 11:46:36 GMT

Hi,

need to modify your set expression and 'and ' wont help you in set analysis

try like below example

sum({<date={">=(=date(addMonths(max(dateField),-8))<=(=date(addMonths(max(dateField),-2))"}>}sales)

Regards

Re: data between the last 8 months and 2 months

bobbydave — Mon, 19 Dec 2016 12:19:07 GMT

= Concat( DISTINCT {<Country = {"= Sum({< Country, [Control Description] = {'Claims Registered'},

sum({[Fiscal Year Month] = {">=(=date(AddMonths(max(today(),-8))<=(=date(AddMonths(max(today(),-2))"}>}

[Record Count]) > 0.2"}>} Country)

I seem to have made a bigger mess

Re: data between the last 8 months and 2 months

PrashantSangle — Mon, 19 Dec 2016 12:30:31 GMT

Hi,

I have just gave you example modify it as per your requirement.

Regards,