All months before my selection

[QSnewbie123]

Hi all,

Im in need of a sales report where I can see the outcome of multiple months, but I only want to chose one month in my selection.

I want to use a pivot table with Dimension = Employee.. Column = Months... Measure =  Sum(sales)

So if I select June, I want the sales report to show "January, February, March, April, May, June".

I struggle with the last part regarding the part to show all month before my selected one. I know I can make "Month=" in my set analysis but that will lead to show July, August and so on...  Should I use something like "Max Month"?

Can anyone help me?

Best regards

[QFabian]

Hi @QSnewbie123 you are close, in the set analysis you have to consider the '<=' part, Here an example  :

Script :

Aux:
Load * INLINE [
Employee, Date, Sales
A, 01-01-2022, 1331
A, 01-02-2022, 1801
A, 01-03-2022, 3573
A, 01-04-2022, 3463
A, 01-05-2022, 2391
A, 01-06-2022, 1584
A, 01-07-2022, 2495
A, 01-08-2022, 1099
A, 01-09-2022, 2454
A, 01-10-2022, 3335
A, 01-11-2022, 1886
A, 01-12-2022, 4212
B, 01-01-2022, 4256
B, 01-02-2022, 3826
B, 01-03-2022, 1481
B, 01-04-2022, 2413
B, 01-05-2022, 1686
B, 01-06-2022, 4852
B, 01-07-2022, 2996
B, 01-08-2022, 2364
B, 01-09-2022, 1289
B, 01-10-2022, 2603
B, 01-11-2022, 3746
B, 01-12-2022, 2011
];

Data:
Load
Employee,
Date,
year(Date) as Year,
month(Date) as Month,
monthname(Date) as MonthYear,
Sales
Resident Aux;
drop table Aux;
exit script;

Then, create a variable 'vMonth', and put this as its expression  '=max(Month)'

2.- Then, in the pivot table chart expression, put this : 

Sum({< Month = {"<=$(vMonth)"} >} Sales)

So, when you select May, its going to show until that selected Month

😉