Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi ,
Can you try this expression :
SUM({<Year=, MonthNum= {"<= $(=max(MonthNum))"}>} Quantity)
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)
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.
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.
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.
Year = means Qlik will exclude this while you calculating Qty.
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
Thank you! it makes sense