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]

Ok,

let me remove all the sensitive information and I will attach it.

Thanks

[sunny_talwar]

Sounds good...

[deniscamh]

Hi Sunny,

Please see the attached qvf file.

On the My new sheet is what I am getting now by simply counting the donors.

On the My new sheet 1is what I did followed your suggestion so it is displayed last month for each year but with no totals and when you expand the year and quarters some fields are not populated.

Thanks for your help.

[sunny_talwar]

Are you not seeing this when you open your file?

Without any changes, I saw a number for Lapsed4+ for 2017-2018... are you not seeing this? What version of Qlik Sense are you using? May be you have to update?

[deniscamh]

Yes I do see the same and I am using June 2017 version.

My problem is when I expand I see some blanks (data disappears) fields

[sunny_talwar]

Checking now

[sunny_talwar]

Adding a DISTINCT seems to have helped

FirstSortedValue(DISTINCT Aggr(Count(DISTINCT [Constituent ID]),FiscalYear,FiscalQuarter,FiscalMonth,[Curent Donor Status]),

-Aggr(Month,FiscalYear,FiscalQuarter,FiscalMonth,[Curent Donor Status]))

[deniscamh]

It looks like it works but let me test it with full application and I will get back to you.

[sunny_talwar]

Sure, if that doesn't work, we will look for a non FirstSortedValue solution....

[deniscamh]

It still does it.

When I expand it is all fine, but when I start to collapse the Quarters the data disappears again in some fields.

Please see below