Summing Pending Sales for only the last date of calendar dimension

[#1]

Hello Qlik Community, thank you for taking your time to answer my question.

My company records pending sales amount by account everyday.

I am trying to create a trending chart of pending sales amount by Year, Year & Month and Year & Quarter. I would only want to sum up the pending sales amount for last date of calendar dimension since the data is snapshot(not transaction).

Is there a way/syntax to only sum up by the last date of the dimension?

For example, if my chart dimension is for Year & Month, I only want to add up the pending sales amount for last dates of each Year & Month (1/31/2016, 2/28/2016, 3/31/2016 etc). I have tried using set analysis but it seems to ignore the dimension in the chart for max date value.

For example, if my chart dimension is for Year & Month, I only want to add up the pending sales amount for last dates of each Year & Month (1/31/2016, 2/28/2016, 3/31/2016 etc). I have tried using set analysis but it seems to ignore the dimension in the chart for max date value.

I've attached a file with mock up data and chart. Thank you for your time.

[MK9885]

I added a different Master Calendar and it's working.

But the problem is, as through Master Calendar it is generating dates until 29th but we have 28th hence we missing sum for Feb. But upon selecting Feb the sum matches.

If any expert can correct the Calendar to remove that extra day from Feb and only until 28th then it will work fine for you.

See the attachment.

[swuehl]

Maybe try FirstSortedValue():

=FirstSortedValue({<[Pending Sales Amount] = {"*"}>} Aggr(Sum([Pending Sales Amount]),  Date), -Date)