I have been spending a lot of time trying to convert my monthly inventory movement into fields that show monthly inventory values. I have been able to get some of my required reports to work as needed, but the remaining charts/tables do not play well when I am filtering on Dimensions and possibly will not work next year (I recently went to a new system and only have current year data). My old Qlik Sense Reports were based on Inventory values and not movement.
I have been able to bring in my data in the basic following format:
Year, Month, YearMonth, TransDate, Location, Item, Lot#, Expiration Date, Quantity, Cost
So, my question to the experts is what formula can I use to aggregate all this movement into actual inventory values?
Since the data is movement, I need to add up all the data from the month of the transaction (TransDate) and every other month that came before it, going back in time to the beginning.
I am currently using Qlik Sense August 2021 version which sits on a server on premise. Any and all help is appreciated.