Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Max value from a range of dates

I have data provided in the following format:

Asset_Date | YTD_ANA | Rev

1/1/2018 | 100 | 10

2/12018 | 101 | 9

3/1/2018 | 99 | 10

The Rev (Revenue) column can be summed over a period, but the ANA value is for a point in time.

I have a Pivot table that has a Fiscal Year as a Dimension (based on the Date) in a column. I use this formula (stored in a variable) to get the YTD ANA value:

(Sum({$<Asset_FiscalMonth={"$(=vMaxFiscalMonth)"}>}[YTD_ANA]))

Asset_FiscalMonth is an Integer calculated in the Load Script.

vMaxFiscalMonth is the last fiscal month from the data, also calculated during load.

If I add a second dimension for Asset_FQtr to the columns, I create a 'drill down' in the pivot table that is desirable. But, the YTD ANA formula doesn't work - I get 0 in that column when the quarter is expanded / visible. I think I understand why, in that I need to get the ANA from the last month in the selection, and not the vMaxFiscalMonth, so I change the formula to:

Sum({$<Asset_FiscalMonth={"$(=Max(Asset_FiscalMonth))"}>}[YTD ANA])

Basically, I'm looking for the YTD_ANA value from the last month respective of the dimension. But, I can't seem to get that formula to return a value. In the Q1 column, it should return the value of '99' from my sample data above.

I'm assuming the same formula would allow me to add the Month to the drill down and still work, so I can drill down through YTD, QTD, and MTD values.

Anyone see a problem w/ the formula?

Sum({$<Asset_FiscalMonth={"$(=Max(Asset_FiscalMonth))"}>}[YTD ANA])

Thanks for any insights!

8 Replies
sunny_talwar

How about this

FirstSortedValue([YTD ANA]), -Asset_FiscalMonth)

Anonymous
Not applicable
Author

Sorry... I should've mentioned that there is other data in the table. I can sum the ANA for the records within a given Month, but not across months, so I need the 'Sum' function here. Thanks!

sunny_talwar

May be this

FirstSortedValue(Aggr(Sum([YTD ANA]), Asset_FiscalMonth), -Asset_FiscalMonth)

Anonymous
Not applicable
Author

I tried your formula w/o success.

Any reason the method I was using (w/ the Set) wouldn't be valid? My original formula seemed to work fine. I think I  just need to substitute in the 'last' available fiscal month to make it work regardless of the date dimension used.

sunny_talwar

From what I understand... Max(Asset_FiscalMonth) differs based on the Asset_FQtr. and you would want to see the Sum only for the max fiscalmonth for each quarter, right?

Anonymous
Not applicable
Author

Not quite. ANA is an average (Average Net Assets) of a holding, and is provided to me as a YTD value for each month on a per holding basis. The ANA value changes for each holding, and I have the ANA for each month. Revenue is another value that I have per holding. I can sum Revenue across months, since the value is for a given month (not as a Running Total), but if I need the YTD ANA, it should be from the most current Month in a selection.

Ex.. If I wants to calculate FYTD numbers for the first quarter, in my app I would select FQ1 and the Sum formula would be the Sum where the FiscalMonths are in 1-3. But, if I wanted FYTD ANA, I would need to only use the ANA numbers from the 3rd month (the max month in the selection).

Hope that makes sense....

Anonymous
Not applicable
Author

Sunny,

I was looking at your replies in this thread - How to make dimensions in chart works for function in set analysis

Your formula:

=Sum({<Lookup_Num={"$(='<=' & (Max(Lookup_Num)-1))"}>}Sales)

..is very similar to what I want to do, but instead of 'Lookup_Num' I'm using a Month value. If I substitute in my field names, I get this formula:

Sum({<Asset_FiscalMonth={"$(='<=' & (Max(Asset_FiscalMonth)))"}>}[YTD ANA])

(I also took out the -1, as I don't think I need it in my case)

In reviewing with a colleague, we think the problem is that the set gets evaluated globally before the Sum, thus returning the wrong Max(Fiscal_Month) value to the Sum expression. I don't seem to get any value back, and I'm not sure how to evaluate that within the dimension.

Any other thoughts? Thanks!

sunny_talwar

In reviewing with a colleague, we think the problem is that the set gets evaluated globally before the Sum, thus returning the wrong Max(Fiscal_Month) value to the Sum expression. I don't seem to get any value back, and I'm not sure how to evaluate that within the dimension.

Yes and this is exactly why I propose FirstSortedValue() or Aggr() instead of set analysis