Display Results based on Last Month

[deniscamh]

Hi all,

I have a pivot table that has Current donor status as a Row Dimension and Fiscal year, Fiscal Quarter and Fiscal Month as Column Dimension. The measure is number of donors, simple expression : count(distinct Donors). Please see screenshot below.

What I am trying to do is for each Fiscal Year instead of showing full year results to show only Last Month result for that Fiscal year and the same for Fiscal Quarter.

What i did so far is created variable vMaxFiscalMonth = Max(FiscalMonth) and added it to expression:

count({<FiscalMonth = {"=$(vMaxFiscalMonth)"}>} distinct Donors).

But the result is still for full year instead of Last Month.If I hard code it, means put 12 instead of variable it works but I cannot hard code it as I need it to be adjusted according to Fiscal year and Fiscal Quarter.

Can anyone let me what I am missing in my expression or any other solution?

Thanks

Denis

[deniscamh]

You mean if i use this expression?

count({<FiscalMonth = {"$(=Max(FiscalMonth))"}>} distinct Donors)

[sunny_talwar]

No, just this

Max([Fiscal Month])

I want to see what the Max(Fiscal Month) is for the 2017-2018 area and want you to check if there is Count(DISTINCT Donor) have any value for that or not....

[deniscamh]

Sure,

The max fiscal month for 2017-2018 is 4 and it has value of donors.

Please see below

[sunny_talwar]

In this chart, can you add Fiscal Month as your dimension?

So, essentially 2 dimensions

1) Fiscal Year

2) Fiscal Month

Expression

Count(DISTINCT Donor)

[deniscamh]

This one works fine if I expand all years and quarters or collapse all years and quarters but if I expand only one year i am still getting null values for some Donor statuses.

[deniscamh]

Sure see below

[deniscamh]

Hi Sunny,

Do you have any updates or thoughts of how to solve this?

Thanks

[sunny_talwar]

I would have to see it to suggest anything else....

[deniscamh]

How do you want to do that?

Do you want me to upload qvd file?

[sunny_talwar]

That would be great if you can do that