topic Max Year and Current Quarter syntax in QlikView

Max Year and Current Quarter syntax

Anonymous — Thu, 07 Jul 2016 00:38:30 GMT

Hello,

I changed my data source from Oracle table to an excel file.

Now my formulas don't work. I assume it is because the numbers are being read as strings.

I have a chart that shows the expenses to date in the current quarter.

I can now do each of them separately but when I try to join them in one calculation to show the max year and current quarter I get an error.

Formula for the Quarter.

=sum( { $< Quarter_number = {'$(vCurrentQuarterNumber)'} >} Actual )

Formula for the Max Year.

=sum( {$<[Posting Fiscal year]={$(=Max([Posting Fiscal year]))}>} Actual )

When I put it back together I get an error.

=sum( {$<[Posting Fiscal year]={$(=Max([Posting Fiscal year]))},{ $< Quarter_number = {'$(vCurrentQuarterNumber)'} >} Actual )

Hopefully I am just missing a coma or something that can be easily adjusted.

Any help would be greatly appreciated. I am on a tight deadline.

Re: Max Year and Current Quarter syntax

sunny_talwar — Thu, 07 Jul 2016 11:16:27 GMT

I think you have an extra set of {$ in there...

=Sum( {$<[Posting Fiscal year]={$(=Max([Posting Fiscal year]))},{ $< Quarter_number = {'$(vCurrentQuarterNumber)'} >} Actual)

Try this:

=Sum({$<[Posting Fiscal year] = {$(=Max([Posting Fiscal year]))}, Quarter_number = {'$(vCurrentQuarterNumber)'}>} Actual)

Re: Max Year and Current Quarter syntax

Usermonk — Fri, 14 Jun 2019 07:51:52 GMT

Hi Sunny,

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.