topic Re: Max() not working in set analysis in QlikView

Max() not working in set analysis

Mon, 05 Mar 2012 17:53:26 GMT

I am trying to get this formula to work to calculate the prior year's data.

I have a chart that I always want to show the maximum year selected in one column and the prior year in the other column based on the maximum year selected minus 1.

For some reason this keeps ignoring the this portion [Fiscal Year] = {"=$(=max([Fiscal Year])-1)"} What am I doing wrong?

=Count({<[Fiscal Year] = {"=$(=max([Fiscal Year])-1)"},[Fiscal Year and Month]=,[Fiscal Year and Quarter]= >} distinct [Order Number])

Max() not working in set analysis

Mon, 05 Mar 2012 18:02:45 GMT

Hi debbiepy,

Could you please try with simple quote instead of double quote?

It should work.

Youssouf

Re: Max() not working in set analysis

Mon, 05 Mar 2012 18:09:52 GMT

I tried that and still no luck.

This is a pivot table with two dimensions for Quarter and Year. I get the cumulated data for each year, one for max(FY) and one for Max(FY)-1,

Re: Max() not working in set analysis

Mon, 05 Mar 2012 18:15:53 GMT

I got this to work

{$(=Max(,1))}

{$(=Max(,2))}

Max() not working in set analysis

Mon, 05 Mar 2012 18:15:57 GMT

Normaly you don't need following constraints

[Fiscal Year and Month]=,[Fiscal Year and Quarter]=

What happen when you remove it?

I guest that is applied after the

[Fiscal Year] = {"=$(=max([Fiscal Year])-1)"},

So that it display all Quarters & Months ignoring the first constraint.

Re: Max() not working in set analysis

Mon, 05 Mar 2012 18:23:07 GMT

If the user selects the current Fiscal Year, or other Fiscal Period I want the chart to still populate the prior year regardless. But I want the chart to still be filtered by Partner just not periods.

THe only period filter is the one to determine the maximum so if use selects 2012, the maxium period will show and the other column will contain 2011 data

Max() not working in set analysis

Mon, 05 Mar 2012 18:35:30 GMT

could you please post an application with a sample data?

so that I could see it physically.

Thanks

Re: Max() not working in set analysis

Mon, 05 Mar 2012 19:42:23 GMT

This works. I had to use a differ value to get maximum year so the selection of Fiscal Year did not affect the formula

Current year:

=Count({<[# of FYs from Current] = {'$(=max(,1))'},[Fiscal Year]=,[Fiscal Year and Quarter]=,[Fiscal Year and Month]=>} distinct )

Prior year: Had to use minus 1 because the max(,2) did not work. I am not sure why.

=Count({<[# of FYs from Current] = {'$(=max(,1)-1)'},[Fiscal Year]= ,[Fiscal Year and Quarter]=,[Fiscal Year and Month]=>} distinct )