Rolling stock count - vision week by week

[leenlart]

Hello!

I have an app with Stock 'situations' by month, and stock movements in and out, day by day.  

I need to create a graph with the weekly stock situation.  

I can get the weeks stock 'situation' with the following formula where I take the situation from the previous month, and add the movements.  Works great.  Yay.  

sum( {$<MOIS_ANNEE={'$(=Date(Monthend(AddMonths(Only(MOIS_ANNEE),-1)),'MMM YYYY'))'}, MOIS=, SEMAINE=, PRECHRO_SIT={'2STK'}, PERIODE_TEMPS=, LNATANA_MVT= >} MTPXREVSTAT_STD_S_SIT)
+ (sum( {$<SENS_MVT={'E'}, DATE_JOUR={">$(=Monthend(AddMonths(Only(MOIS_ANNEE),-1)))"}, MOIS=, SEMAINE=, PERIODE_TEMPS= >} MTPXREVSTAT_STD_S_MVT)
- sum( {$<SENS_MVT={'S'}, DATE_JOUR={">$(=Monthend(AddMonths(Only(MOIS_ANNEE),-1)))"} , MOIS=, SEMAINE=, PERIODE_TEMPS= >} MTPXREVSTAT_STD_S_MVT))

Where, in my calendar : MOIS Is the month, SEMAINE Is the Week number.  And DATE_JOUR is the date.

When I try and turn this into a graph week by week, I only get the previous month data for the first week of the month.  To see what is going on, I split it out, and turned it into a table format.  Which gives this : 

Where am I going wrong ?

Thanks for your help!  

Regards,

[Oleg_Troyansky]

Hi @leenlart ,

This is just a hunch, but maybe it's worth checking into... The function MonthEnd() is tricky - it gives you the last millisecond of the date, not a round number. For example, if the month end date's number is 42123, the MonthEnd() function would return 42123.9999999 or something like that.

So, your MonthEnd() would never match your calendar dates, assuming that they are stored as whole numbers.

The solution is rather simple - wrap your MonthEnd functions in the Floor() function, and that will trim the unnecessary decimal digits.

You should consider coming to my Qlik Expert Class in Vienna on September 22-24 - among many other advanced techniques, I will teach the topic of calculating running inventory balances and inventory aging in the data load script.

Cheers,

Oleg Troyansky

[leenlart]

Hello, 

@Oleg_Troyansky   Thanks for your response.  I tried your idea, but that didn't change anything.   Also, thanks for the info for your expert class in Vienna, I WISH I could come!!  A.) the topic is perfect for what I'm working on and B.) Vienna!!  But alas, my company didn't accept my expense request.  Maybe next time.  Let me know if there is a video or presentation that I can take a look at afterwards.  

Maybe I can detail my tables a little more and someone will have an idea.  

Situations table : date_sit, situation id, item, quantity, unit, volume in kg, value.  These rows only exist for the last day of each month.  

Movements table : date_mvt, mouvement id, item_id, warehouse, quantity_mvt, unit_mvt, volume in kg, value_mvt.  These rows exist for all in and out changes in stock.  

Calendar table : date_id, year, month, week, day of week name

The link between these tables is in a pivot table in the middle with the id field, the date field, the item field.  So we have a lovely snowflake.  

All my "date" fields are formatted in the load script to be numerical, with no time stamp.  

(IE 45839 = 01/07/2025)

Any thoughts ?  

[Oleg_Troyansky]

Hi @leenlart ,

I don't understand your data well enough to offer a solution. Generally speaking, I'd recommend linking your tables in the data model, not in a pivot table. Calculating running balances would also be easier and faster if it's done in the data load script. You can use functions Peek() and Previous() to fetch balances from previous rows of data for the purpose of calculating the new balance.

If you don't mind learning this stuff using QlikView, you could check out my book QlikView Your Business, where I teach Qlik data modeling, from the basics to advanced methodologies, including the process of building running balances for inventory apps.

Cheers,

Oleg Troyansky