Hey! I'm new with QlikView and I'm having troubles with the following problem:
I have a Stock table from the B2b clients where Stock per product is indicated as a current state quantity, this means, for every month, I only need to sum the stock for the last recorded day, so I'm using the following expression (using month and retail as my dimensions):
Current Year:
Sum({$<PeriodID = {"<=$(=Max(PeriodID))"},
Year = {"$(=Max(Year))"}, Month =, Day =,
Date ={"=Day(Date) = Day(MonthEnd(Date))"} > } [stock])
Last Year:
if(Month<=month(today()),
Sum({$<PeriodID = {"<=$(=Max(PeriodID)-12)"},
Year ={"$(=Max(Year)-1)"}, Month =,
Date ={"=Day(Date) = Day(MonthEnd(Date))"} > } [stock])
,
Sum({$<Year =, Month =, Date =
{"=Day(Date) = Day(MonthEnd(Date))"} > } [stock])
)
)
Where: PeriodID = (Year*12)+Month
Date = DD-MM-YYYY
1) There is an issue here because, with this code, I'm not able to show the stock for the current month as I obviously don't have data when cheking for Day(MonthEnd(Date)).
2) The other problem is, when trying to focus on the business weeks, for example, the 2018 5th business week goes from January 29th to February 4th and during 2017 goes from Jan 30th to Feb 5th. I want to show the stock for the last day of the Month of January and the stock for the last day of the business week (February 4th).
Hope you can guide me with this problems, thanks in advance.
