topic Re: Problem with Max(Month)!!! in QlikView

Problem with Max(Month)!!!

Fri, 12 May 2017 14:51:59 GMT

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

krishnacbe — Fri, 12 May 2017 15:03:49 GMT

Try below expression.

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

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

Re: Problem with Max(Month)!!!

Frank_Hartmann — Fri, 12 May 2017 15:06:01 GMT

Or maybe like that:

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

Re: Problem with Max(Month)!!!

Fri, 12 May 2017 15:25:38 GMT

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

vishsaggi — Fri, 12 May 2017 15:26:22 GMT

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

Sergey_Shuklin — Fri, 12 May 2017 15:26:31 GMT

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

Fri, 12 May 2017 15:38:06 GMT

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

Re: Problem with Max(Month)!!!

vishsaggi — Fri, 12 May 2017 15:55:23 GMT

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

Fri, 12 May 2017 15:56:43 GMT

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

Anonymous — Fri, 12 May 2017 16:19:40 GMT

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

Digvijay_Singh — Fri, 12 May 2017 16:51:28 GMT

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 ];

Re: Problem with Max(Month)!!!

Fri, 12 May 2017 17:13:35 GMT

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

Anonymous — Fri, 12 May 2017 17:21:42 GMT

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.

Re: Problem with Max(Month)!!!

Fri, 12 May 2017 17:28:02 GMT

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

Re: Problem with Max(Month)!!!

Fri, 12 May 2017 17:37:16 GMT

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