topic Re: Help: Expression to sum values on max date for a month or period in QlikView

Help: Expression to sum values on max date for a month or period

Anonymous — Tue, 10 Jul 2018 16:12:11 GMT

Hello.

I'm having problems with a dynamic table expression, where I want to show the stock for the last recorded day of the month or period. The stock values are given by product per day, so I need to sum only the stock per product for the last recorded day.

Currently I am doing some testing with the following results:

Given:

PeriodID = (Year*12)+Month

Date = DD-MM-YYYY

test2:

if(month = max(month),

Sum({$<PeriodID = {"<=$(=Max(PeriodID))"}, year =

{"$(=Max(year))"}, month =, day =, FECHA_DATOS =

{"=Day(FECHA_DATOS) = Day(MonthEnd(FECHA_DATOS))"} > } [stock])

if(Day(FECHA_DATOS) = Day(MonthEnd(FECHA_DATOS)),

Sum({$<PeriodID = {"<=$(=Max(PeriodID))"}, year ={"$(=Max(year))"}, month = {"$(=Max(month))"},

FECHA_DATOS = {"=Day(FECHA_DATOS) = Day($(=Max(FECHA_DATOS)))"} > } [stock]))

)

test:

if(Day(FECHA_DATOS) = Day(MonthEnd(FECHA_DATOS)),

Sum({$<PeriodID = {"<=$(=Max(PeriodID))"}, year =

{"$(=Max(year))"}, month =, day =, FECHA_DATOS =

{"=Day(FECHA_DATOS) = Day(MonthEnd(FECHA_DATOS))"} > } [stock])

Sum({$<PeriodID = {"<=$(=Max(PeriodID))"}, year ={"$(=Max(year))"}, month =, day =,

FECHA_DATOS = {"=Day(FECHA_DATOS) = Day($(=Max(FECHA_DATOS)))"} > } [stock])

)

STOCK Actual:

test+test2

If I filter for the 5th comercial week (2017: January 30th to Febraury 5th & 2018: January 29th to Febraury 4th) I get the following results:

STOCK Actual is just a way to aggregate test2 and test into a single column (looking to hide them afterwards).

Then, if I take the week filter, I get the following data by month:

Where there is a evident difference between test2 and test.

This is why I'm looking for your help, to fix one of the above expressions and end up with a single column that shows the monthly and weekly (business week) stock.

Thanks in advance, best regards.

Re: Help: Expression to sum values on max date for a month or period

sunny_talwar — Tue, 10 Jul 2018 18:06:08 GMT

I am not entirely sure I follow... what is right and what is wrong in the above images? With regards to what is wrong, what is the expected output?

Re: Help: Expression to sum values on max date for a month or period

Anonymous — Wed, 11 Jul 2018 15:10:06 GMT

Hello stalwar1‌, sorry, english ain't my native language so it's harder to express myself the right way.

On the first picture, my biggest problem is how to build a unique expression to show the result of "test2" and "test" on a single column. Adding both columns is not a good solution because, when I deselect the business week, I get the same value for "test" and "test2" (none of them gets a 0) and the sum ends up representing 2 times the real stock.

What I need is a expression that does the following on a single column:

- If the period 22nd to 26th of January is selected, only sum the stock of the 26th of January.

- If the period of 29th of January to the 4th of Febraury is selected, do the sum of stock from the 31th of January (last day of the month) and the sum of stock for the 4th of Febraury (last day of the selected period).

- If a day is selected, show the sum of stock for that day.

- If there is no selection made, show the sum of stock for the last day of each month.

From the second picture and some analysis, I realized that the expression "test2" is better than "test", as "test2" works ok indistinctly of the year, but "test" doesn't give the right results for 2018. I checked every value against the original data provided by the B2b companies.

I hope I express my problem better this time, thanks in advance.