Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

deniscamh
Contributor

Display Results based on Last Month

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.Donor Status.JPG

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Display Results based on Last Month

Here is the other option

If(SecondaryDimensionality() = 1,

Sum(Aggr(If(Month = Max(TOTAL <FiscalYear, [Curent Donor Status]> Month), Count(DISTINCT [Constituent ID])), Month, FiscalYear, [Curent Donor Status])),

If(SecondaryDimensionality() = 2,

Sum(Aggr(If(Month = Max(TOTAL <FiscalYear, [Curent Donor Status], FiscalQuarter> Month), Count(DISTINCT [Constituent ID])), Month, FiscalYear, [Curent Donor Status], FiscalQuarter)),

If(SecondaryDimensionality() = 3,

Sum(Aggr(If(Month = Max(TOTAL <FiscalYear, [Curent Donor Status], FiscalQuarter, FiscalMonth> Month), Count(DISTINCT [Constituent ID])), Month, FiscalYear, [Curent Donor Status], FiscalQuarter, FiscalMonth)))))

45 Replies
vinieme12
Esteemed Contributor II

Re: Display Results based on Last Month

try

vMaxFiscalMonth=  =Max(TOTAL FiscalMonth)   (should have an equal sign in the variable definition)

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

deniscamh
Contributor

Re: Display Results based on Last Month

Thanks for replay Vineeth,

Your solution works fine with the past years at the last month for theses years is 12 but it would not work for current year as the last month for current year is 8.

Also it does not work for Quarters as i need last moth for each quarter.

Do you have any other ideas?

Thanks

vinieme12
Esteemed Contributor II

Re: Display Results based on Last Month

For CurrentYear

vMaxFiscalMonth=  =Max({<YearField = {$(=Year(today())) }>}TOTAL FiscalMonth)


Also it does not work for Quarters as i need last moth for each quarter.

     Create this flag in a MasterCalendar

agigliotti
Honored Contributor II

Re: Display Results based on Last Month

maybe this:

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

deniscamh
Contributor

Re: Display Results based on Last Month

Hi Andrea,

Thank you for replay

It works partially.

When I add the expression to the pivot table it does not display current year as it does not have month 12 but it would display it when you select it. I need it to be displayed as well according to last month for this year results.

Also only Q4 is displayed unless you select specific Quarter. I need all Quarters to be displayed with the results of last month for that quarter.

Do you have any ideas of how to do that.

agigliotti
Honored Contributor II

Re: Display Results based on Last Month

let's try with the below expression:

if( FiscalMonth = Max(FiscalMonth), count(distinct Donors) )

deniscamh
Contributor

Re: Display Results based on Last Month

Thanks Vineeth, but I cannot create separate variable for current year I need one for all fiscal years as I am using the same dimension in pivot table.

deniscamh
Contributor

Re: Display Results based on Last Month

In that case it would work only if you select a month otherwise the expression would not give any result...

MVP
MVP

Re: Display Results based on Last Month

May be try this

FirstSortedValue(Aggr(Count(DISTINCT Donors), [Donor Status], [Fiscal Month]), -Aggr([Fiscal Month], [Donor Status], [Fiscal Month]))

Community Browser