Set Analysis not working

[svendsenfe]

Hello everyone,

I am trying to create a set analysis which is not giving me any results, and I am not too sure why.

I have two tables

   STOCKS with Dates, Quantity, ProductID....

    CALENDAR with Date, Year,  Month....

I would like to sum all the Stock Quantities for a Date that is the greatest less than another Date x (in my case, the end of the month). In other words, get the max date which has data and is less than the end of the month, and sum all the stock Quantity on that day.

To do that I have done the following: 

vMonthLastDay  = MonthEnd(Date)

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

Here, vMonthMaxDate does give me the date I desire, and seems to work perfectly.

However, when I use it in the following:

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

I get no results. Could anyone point out where am I failing? why doesnt it sum the Quantity when I am providing the rigth date and there is data?

Thank you,

Federico Arribas

[rbartley]

Hi Federico,

Have you applied any other filters that could influence the results?

[Marcel_Garcia]

Is the date format the same for 'Date' field and 'vMonthMaxDate ' variable? That typically has giving my lots of problems.

[svendsenfe]

Hello @rbartley  and @Marcel_Garcia,

No, I am just grouping by YearMonth.

Here you have a table of how it looks like.

As you can see $(vMonthMaxDate) is calculated properly.

But when I use vQuantityMonth = sum({<Date= {'$(vMonthMaxDate )'} >} Quantity)

I get the following error: 

 

When all the , and ) are correct.

Thank you,

Federico Arribas.

[agigliotti]

Hi @svendsenfe ,

Maybe this:

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

I hope it can helps.

Best Regards

[svendsenfe]

Thank you for the help! 

However using double quotes I am still getting the same error as below

Thank you,

Federico Arribas.

[rbartley]

How about this:

sum( {< Date = {"$(=vMonthMaxDate)"} >} Quantity )?

Did you include the = in the definition of vMonthMaxDate?

[svendsenfe]

Still get the same issue 😞 

I am not sure why since I can calculate vMonthMaxDate perfectly, and then I am just suming all quantities on such date.

In the definition of vMonthMaxDate I do not have the =, if I do so it stops being a dynamic variable and is set on a result (Max date of all orders)

Thank you,

Federico Arribas.

[vinieme12]

the variable definition is the problem here, corrected

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

also why Quantity= {"> 0"} ? I feel like this should be removed and added to the vQuantityMonth  instead

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

[svendsenfe]

Hello @vinieme12 thank you for your answer!

The issue with changing to

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

is that Date is required to be less than $(vMonthLastDay), not equal. If for example the end of the month has no Quantity, but you are asking to get that Date, the result of vMonthMaxDate will be null, because no such date exists. That´s why I need  Date= {"=Date< '$(vMonthLastDay)'"}

Moreover, Quantity= {"> 0"} has to be in vMonthMaxDate because to get the max date we need such date to have a Quantity. If I add the condition in vQuantityMonth  instead, the date we are looking for will already be wrong.

That´s why 

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

After testing it, it seems to work perfectly and as expected. The proble comes when calling it inside vQuantityMonth  

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

What do you think?

Federico Arribas.