14 Replies Latest reply: May 12, 2017 1:37 PM by Dr.Maks Clay

# Problem with Max(Month)!!!

Hi, friends! )

I need your consultation for the folowing problem.

I have "Sales" Table, wich topology looks like this:

ID_Product, Price, SaleDate, Qiantity

I need to show in another table the products wich were sold in last month. The Last SaleDate is-June.

I have tried to do this:sum({<Month(SaleDate)={'\$(=max(month(SaleDate)))'}>} Quantity) but it does't works!

Can anybody help me?!

• ###### Re: Problem with Max(Month)!!!

Try below expression.

sum({<Month(SaleDate)={'=\$(=max(Month(SaleDate)))'}>} Quantity)

or

sum({<Month(SaleDate)={'=\$(max(Month(SaleDate)))'}>} Quantity)

• ###### Re: Problem with Max(Month)!!!

May be, I could't explaned what I wanted, but I'll try again.
I want to get something like this: As I said, June is the last Month in all my DataBase, the last sale was in 13/06/2013.

The result should be:

Product_ID Last_Month
1                  24 (Product Quantity which I sold in June)

4                  2 (Product Quantity which I sold in June)

How to do this?

• ###### Re: Problem with Max(Month)!!!

Or maybe like that:

sum({<SaleDate={'=\$(=max(Month(SaleDate)))'}>} Quantity)

• ###### Re: Problem with Max(Month)!!!

May be this: Not sure might have to tweak around the expression near date.

= sum({< SaleDate = { " =\$(= '>=' & MonthStart(MonthStart(Max(SaleDate))-1) & '<'  & MonthStart(Max(SaleDate)) )"}>} Quantity)

• ###### Re: Problem with Max(Month)!!!

Hello, Dr.Maks Clay!

Please look at the picture, is this what you want? (You need to create a variable with max date - it's usefull to use it over the project)

• ###### Re: Problem with Max(Month)!!!

I'll try to explane better, what I want to get!
For example: I have a Pencils, books, pens and notebooks wich I sale every day. Now, I want to know, how many Pencils, Books, Notebooks i have sold in Last Month!

P.S: Last month is June, but there could be situation when i dont sale any books in june but sale pens.

Product            Jan     Feb     Apr      May         Jun           LastMonth

Pens                  20       2       0          22             1                    1

Books                 3        12      1         12             12                  12

Pencils                0          1      11        0              13                  13

Notebooks           1          3       12        11             11                 11

• ###### Re: Problem with Max(Month)!!!

If none of the above worked for you,

It would be easy if you can share a sample app with expected output.

• ###### Re: Problem with Max(Month)!!!

That's what I want to get!

Product            Jan     Feb     Apr      May         Jun           MaxMonth (In this situation is June)

Pens                  20       2       0          22             1                    1

Books                 3        12      1         12             12                  12

Pencils                0          1      11        0              13                  13

Notebooks           1          3       12        11             11                 11

• ###### Re: Problem with Max(Month)!!!

Hi,

AS suggested by others you should have dual month field so that numeric comparison can be made.

I just tried to handle your sample through pivot table, just had to add Monthno field but its not needed if your month field is extracted from the date field. Check this -

Script used -

Table:

CrossTable(Month,Value)

Product,            Jan,    Feb,    Apr ,    May ,        Jun

Pens,                  20  ,    2  ,    0  ,      22  ,          1

Books  ,              3  ,      12 ,    1  ,      12  ,          12

Pencils ,              0 ,        1  ,    11 ,      0 ,            13

Notebooks  ,        1  ,      3  ,    12  ,      11  ,          11  ];

Left Join(Table)

Month, MonthNo

Jan,1

Feb,2

Apr,4

May,5

Jun,6 ];

• ###### Re: Problem with Max(Month)!!!

I would probably create a variable for the max month and refer to that in m set analysis.

Something like:

Let vMaxSalesMonth=month(max(SalesDate));

(But variable could be different based on what your data looks like.)

Then your expression would be something like.

sum({<Month(SaleDate)={'\$(vMaxSalesMonth)'}>}Quanitity)

Really, I would probably add a month field in your script so that it would be even simpler.

sum({<MonthDate={'\$(vMaxSalesMonth)'}>}Quanitity)

• ###### Re: Problem with Max(Month)!!!

Thanks a Lot, Wallo!
1) What about the situation, when I didn't sold anything in June? This Method will give me 0 or just "-" instead any number?;

2) When I want to get previous Month, I should do something like this?
let PreviousMonthSale=MONTH(MAX(SalesDate))-1;

In expression: sum({<Month(SaleDate)={'\$(PreviousMonthSale)'}>}Quanitity)

Am I right?

• ###### Re: Problem with Max(Month)!!!

1)If a product sold nothing in June I would think you would want it to show as 0.  Not sure what default will show (0 or blank) but you could probably add alt function to make sure it shows as a 0.  Like:

alt(sum({<Month(SaleDate)={'\$(vMaxSalesMonth)'}>}Quanitity),0)

2)for previous month to be safe, the variable would be