Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

14 Replies
krishnacbe
Partner - Specialist III
Partner - Specialist III

Try below expression.

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

or

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

Frank_Hartmann
Master II
Master II

Or maybe like that:

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

Not applicable
Author

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?

vishsaggi
Champion III
Champion III

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)

Sergey_Shuklin
Specialist
Specialist

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)

max_date.png

Not applicable
Author

Hi, Sergey! Thanks a lot for your answer.

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

vishsaggi
Champion III
Champion III

If none of the above worked for you,

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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)