Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
matancha
Creator
Creator

Pivot Table - Start Of Month Warehouses Balance

Hello all.

Please look on the attached file.

In this file I have: Parts, Orders for parts, current warehouse balance for each part, due-date calendar, and table with accumulated order quantity for each part for each order-row.

I need to calculate the warehouses balance on the start of the month, i.e - i need to "Copy" the previous 'EOM Warhs Balance' column,

But  - without use functions like - "Before" & "After", so if user select a specific months, he will get real data (not depend on the previous

columns) .


The 'EOM Warhs Balance' expression is:

Sum(WarehousesBalance.Balance) - FirstSortedValue(AccumulateBalanceOpenOrders.AccTBalance, -1 * AccumulateBalanceOpenOrders.RowNo)

In Short:

WarhouseBalance_StartOfMonth1.JPG.jpg

and when choosing YM =  '2014-10' the table will show:

WarhouseBalance_StartOfMonth2.JPG.jpg

I think that a combination of 'Firstsortedvalue()' and 'Aggr()' functions will solve this problem but I don't know how....

Hope I was able to clearly explain the problem...

Many thanks for any help -

Matan.

5 Replies
sujeetsingh
Master III
Master III

Matan ,

It is just due to the scene that you have only one expression giving value for a time period

sujeetsingh
Master III
Master III

See the sample you have posted

sujeetsingh
Master III
Master III

Mantan,

You have to use Peek function in script bashed on the date and derive the previous values of the field to be used in the expression and then use it .

matancha
Creator
Creator
Author

sujeetsingh - thanks for your quick answers,

but i didn't see any changes in my expressions.

I only saw change in table type (pivot --> straight).

Matan

matancha
Creator
Creator
Author

Can you show me an example?

I couldn't understand what you're saying.

Note that the time dimension is cyclic: YM | YQ | Year | Date ...

Thanks again...

Matan.