Set Analysis max date less than

[svendsenfe]

Hello everyone,

I am having an issue coming up with a Set Analysis and was wondering if anyone could help.

I have two tables:

- CALENDAR with all DATES from 2000 till now with information such as MonthNum, WeekNum, Year....

- STOCK with Dates, Quantity, Import....

I am looking for the sum of all variables "Quantity" on a date that follows the following conditions:

     - Date has stock (exists in STOCK table)

     - Date being the greatest less that another date x (In my case last day of the month).

Here is what I have:

= sum( {<date = {"max({1 - <date = { <= '$(vMonthLastDay)'}, StockID = {"*"}>} date)"}>} Quantity)

My thinkin: 

-  sum all Quantity

- where date is max with the conditions:

            - all posible dates  - (is less that vMonthLastDay and StockID has a value)

Could anyone point out where am I failing?

Thank you,

Federico Arribas.

[vikasmahajan]

Hi ,

Can you try this expression :

SUM({<Year=, MonthNum= {"<= $(=max(MonthNum))"}>} Quantity)

[vinieme12]

 where date is max with the conditions:

            - all posible dates  - (is less that vMonthLastDay and StockID has a value)

1) is less that vMonthLastDay : if  you want to exclude the last day of the month for all months, then i suggest you create a flag in your Calendar

as below

if(Date=MonthEnd(Date) ,1 ,0)  as flag_MonthEnd

2) and StockID has a value :  check if Quantity>0   

You final expression should be as below

=sum( {<  flag_MonthEnd={0}  , StockID={"=Quantity>0"}>} Quantity) 

[svendsenfe]

This works very well Thanks!

Only issue I see is that I cannot modify it to see it by week for example.

Or if a month has no data it will put 0, instead of taking last month. 

[svendsenfe]

Hello! Thank you for your answer.

With this set Analysis all I am getting is the sum of quantity of all the Month. Not getting the last date of such month.

One questions, what is the meaning of  "Year=,"? why is there nothing after the =, I have not been able to find anothing about it. 

[svendsenfe]

@vikasmahajan @vinieme12 

What I have tried so far is

= sum({

<Date= {" = Date = '$(=Max({<Date = {" = Date < '$(vMonthLastDay)'"}>} Date))'"}
, StockID={"=Quantity>0"}>

} Quantity)

However, The Date I am getting is not the last one with information in the month... I am failing in the part:

Date = {" = Date = '$(=Max({<Date = {" = Date < '$(vMonthLastDay)'"}>} Date))'"}

Could you please have a look and see if you could point out where I am failing?

This way, if it works, I could change vMonthLastDay by vWeekLastDay and will work and be able to look by every x amout of time I want.

Thank you,

Federico Arribas.

[vikasmahajan]

Year =  means Qlik will exclude this while you calculating Qty.

[svendsenfe]

Hello everyone, 

I have done the following:

Create variables:

vMonthLastDay  = MonthEnd(Date)

vMonthMaxDate = date(Max({< Date= {"=Date< '$(vMonthLastDay)'"}, Quantity= {"> 0"}>} Date))

vQuantityMonth = sum({<Date= {'$(vMonthMaxDate )'} >} Quantity)

vMonthMaxDate seems to work properly.  It returns the max date less than the end of the month where there is some quantity. 

However, when i try to use vQuantityMonth , it does not sum it properly.

Does anyone have any idea why? @vikasmahajan @vinieme12 

Thank you,

Federico Arribas

[svendsenfe]

Thank you! it makes sense