topic Re: Pivot Table - Start Of Month Warehouses Balance in QlikView

Pivot Table - Start Of Month Warehouses Balance

matancha — Mon, 14 Jul 2014 07:55:41 GMT

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:

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

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.

Re: Pivot Table - Start Of Month Warehouses Balance

sujeetsingh — Mon, 14 Jul 2014 08:03:02 GMT

Matan ,

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

Re: Pivot Table - Start Of Month Warehouses Balance

sujeetsingh — Mon, 14 Jul 2014 08:03:44 GMT

See the sample you have posted

Re: Pivot Table - Start Of Month Warehouses Balance

sujeetsingh — Mon, 14 Jul 2014 08:10:54 GMT

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 .

Re: Pivot Table - Start Of Month Warehouses Balance

matancha — Mon, 14 Jul 2014 08:14:32 GMT

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

Re: Pivot Table - Start Of Month Warehouses Balance

matancha — Mon, 14 Jul 2014 08:17:58 GMT

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.