Using set analysis to get the count of previous month in a pivot table

[tinkerz1]

Hi,

I have set up a simple count expression using a pivot table and would like to get the numbers for the previous month.

This is so I can get a % change between last and this month.

The set analysis is not bringing the count of all reference numbers less than the date selected, it seems to pivot them out across the month pivot.

Where am I going wrong? I this more of an suitable aggr formula?

I expect to see 26 in all the cells where the set analysis is.

Thanks.

[vinieme12]

Try

count(TOTAL <TABLE> {$<Month={"$(='<=' & Date(Today(),'DD/MM/YYYY'))"}>} Reference)

OR Preferably below,

count(TOTAL <TABLE>  {$<Month={"<=$(=Date(Today(),'DD/MM/YYYY'))"}>} Reference)

[tinkerz1]

Thanks Vineeth that works.

My next step is to bring forward last months data into the pivot table when month is selected.

count(TOTAL <TABLE> {$<Month={"=$(=date(Addmonths([Month],-1),'DD/MM/YYYY'))"}>} Reference)

I have updated the original qvw, I don't understand why when using addmonth why the balance of the previous number is not working

Thanks

[vinieme12]

remove the EqualSign in RED

count(TOTAL <TABLE> {$<Month={"=$(=date(Addmonths([Month],-1),'DD/MM/YYYY'))"}>} Reference)

and just use

count(TOTAL <TABLE> {$<Month={"$(=date(Addmonths([Month],-1),'DD/MM/YYYY'))"}>} Reference)

The Little Equals Sign

[vinieme12 · Accepted solution]

Also as you are using monthend dates

use the below expression

count(TOTAL <TABLE>  {$<Month={"$(=date(MONTHEND(Addmonths([Month],-1)),'DD/MM/YYYY'))"}>} Reference)

[tinkerz1]

Hi,

Can you explain briefly what TOTAL <fieldname> does?

Did it group everything around <TABLE>?

[vinieme12]

Yes, that is correct