Find this Quarter and Last Quarter using Expressions

[Usermonk]

Hi,

I need to calculate the "count of the Orders" based on "device_type" for this Quarter and last Quarter.  

I tried below one for this Quarter but that is always showing count 0 instead of the actual value.

Count({<ordered_on = {">=$(=Date(Quarterstart(max(ordered_on)), 'MM/DD/YYYY'))<=$(=date(max(ordered_on), 'MM/DD/YYYY'))"}>}device_type), where date format in my db looks like "06/01/2019 05:05:25 PM"

I see more examples using Quarter filters, but i don't want to use Quarter filters in my project. I need it to be applied in a column using Expressions.  I tried modify most of the expressions to my needs from the community and nothing worked. I am looking for the working Expressions 

Please guide me to achieve this. 

[mato32188]

Hi Usermonk,

I would suggest you to do it in 2 steps:

• in script editor create a new field: Year(ordered_on)*4+ceil(month(ordered_on)/3) as QuarterID right in your table
• in expression editor
  • this Quarter: =count({<QuarterID = {'$(=Year(Today())*4+ceil(month(today())/3))'}>}device_type)
  • last Quarter: =count({<QuarterID = {'$(=Year(Today())*4+ceil(month(today())/3)-1)'}>}device_type)

Thanks.

Martin

[mato32188]

Or 

• in script editor create a new field: Year(ordered_on)*4+ceil(month(ordered_on)/3)-(Year(Today())*4+ceil(month(today())/3))as QuarterID right in your table
• in expression editor
  • this Quarter: =count({<QuarterID = {0}>}device_type)
  • last Quarter: =count({<QuarterID = {'-1'}>}device_type)

[sunilpaul]

Hi 

Can you share sample data...

Regards,

Sunil Paul