topic Re: Find this Quarter and Last Quarter using Expressions in QlikView

Find this Quarter and Last Quarter using Expressions

Usermonk — Sat, 16 Nov 2024 03:18:13 GMT

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.

Re: Find this Quarter and Last Quarter using Expressions

mato32188 — Fri, 14 Jun 2019 08:23:48 GMT

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

Re: Find this Quarter and Last Quarter using Expressions

mato32188 — Fri, 14 Jun 2019 08:34:15 GMT

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)

Re: Find this Quarter and Last Quarter using Expressions

sunilpaul — Fri, 14 Jun 2019 12:22:34 GMT

Can you share sample data...

Regards,

Sunil Paul