Problem with Max(Month)!!!

[#1]

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?!

[Digvijay_Singh]

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)

Load * inline [

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)

Load * inline [

Month, MonthNo

Jan,1

Feb,2

Apr,4

May,5

Jun,6 ];

[#3]

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?

[Anonymous · Accepted solution]

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

let PreviousMonthSale=month(addmonths(MAX(SalesDate),-1));

the way you had it, you might have a problem if the current month is in January.

[#5]

Perfect, Wallo! Thanks A lot For your Help! )

[#6]

Thanks A Loy, Digvijay! Your script was helpful for me!