Hi All, I have a query rule which takes 2 parameters: a start date an

aswinb over 11 years ago

Hi All,

I have a query rule which takes 2 parameters: a start date and an end date. It will give me a list of items that were created between the start date and the end date. For instance if I pass in (2009/5/1) as start date and (2014/2/3) as end date it will give me an accumulation of values that occured between those dates. I would like to group that data by months. For instance how much values were created in the month of January, February, ....etc between the dates I passed in to the query rule. So far I have been able to get a list of dates like (2009/5/1, 2009/5/3, 2009/5/4, 2009/6/14,....) which denotes that during those dates a value was created. But I want to be able to distinguish the count of values that were created in the month of 2009/5 and 2009/6 ,....etc till the end date. My goal is to create a tempo report and inside that I want to create a bar graph with the list of values as data and months/year as the categories. I have been t...

Discussion posts and replies are publicly visible

0 aswinb over 11 years ago

...rying a whole lot for two days but couldn't find a solution yet. Can anybody help?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 venkats533 over 11 years ago

have an array of size 12 initialized to all zeros. write a function that will return 1 for the month index if the date falls in that month. add the array cumulatively for all elements using apply, then at the end you will have an array of 12 numbers assigned with total count for each month.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Michael Chirlin
Appian Employee
over 11 years ago

I threw together these few rules that should give you a list of the months with their respective number in a ad hoc date object. Let me know if you need further help.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Michael Chirlin
Appian Employee
over 11 years ago

IndexOfMonthYear

with(
monthIndices: wherecontains(
month(ri!date),
tointeger(index(ri!monthYears, "monthValue", null))
),
if(
isnull(local!monthIndices),
"",
index(wherecontains(
year(ri!date),
tointeger(index(
index(ri!monthYears, "yearValue", null),
local!monthIndices,
null
))
), local!monthIndices, null)
)
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Michael Chirlin
Appian Employee
over 11 years ago

ReduceMonthYear

=with(
index: rule!IndexOfMonthYear(ri!accumulator, ri!date),
newValue: if(
local!index <> "",
{
monthValue: index(index(ri!accumulator, local!index, ""), "monthValue", month(ri!date)),
yearValue: index(index(ri!accumulator, local!index, ""), "yearValue", year(ri!date)),
number: tointeger(index(index(ri!accumulator, local!index, 0), "number", 0)) + 1
},
{
monthValue: month(ri!date),
yearValue: year(ri!date),
number: 1
}
),
if(
local!index <> "",
updatearray(
ri!accumulator,
local!index,
local!newValue
),
append(
ri!accumulator,
local!newValue
)
)
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Michael Chirlin
Appian Employee
over 11 years ago

CalculateDatesPerMonth

=with(
dates: cons!datesList, /* Plug in your date list here */
reduce(
rule!reduceMonthYear,
{},
local!dates
)
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Michael Chirlin
Appian Employee
over 11 years ago

ReduceMonthYear has two inputs (accumulator: Any Type, date: Date)
IndexOfMonthYear has two inputs (monthYears: Any Type, date: Date)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 aswinb over 11 years ago

Hey Michael,

Thank you so much. It's working but one more little thing I need. Right now the output I get is like this:

monthValue = 3, yearValue = 2014, number = 1,
monthValue = 3, yearValue = 2014, number = 1,
monthValue = 3, yearValue = 2014, number = 1,
monthValue = 3, yearValue = 2014, number = 1,
monthValue = 3, yearValue = 2014, number = 1,
monthValue = 4, yearvalue = 2014, number = 1,
monthValue = 4, yearvalue = 2014, number = 1,
monthValue = 4, yearvalue = 2014, number = 1,

Can you tell me how I can get the count of 'number' for the same month and the same year. For instance if the month is 3 and year is 2014 I would like to get the count of all the 'number' fields that fall into that category. thank you for help. I deeply appreciate it.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Michael Chirlin
Appian Employee
over 11 years ago

When I run the rule with these dates:

10/04/2014, 18/04/2014, 25/04/2014, 15/05/2014, 19/04/2014, 10/04/2015, 10/05/2014

My output is:

[monthValue:4,yearValue:2014,number:4], [monthValue:5,yearValue:2014,number:1], [monthValue:4,yearValue:2015,number:1], [monthValue:5,yearValue:2014,number:1]

What are you using for your dates?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Michael Chirlin
Appian Employee
over 11 years ago

Try replacing the IndexOfMonthYear rule with:

with(
monthIndices: wherecontains(
month(ri!date),
tointeger(index(ri!monthYears, "monthValue", null))
),
if(
isnull(local!monthIndices),
"",
index(
local!monthIndices,
wherecontains(
year(ri!date),
tointeger(index(
index(ri!monthYears, "yearValue", null),
local!monthIndices,
null
))
),
null
)
)
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel