Periods of dates in pivot table

[#1]

Hi guys!

I have a table with a number of items came in or out in each stock for each day.

For example:

Date	Stock	Item	inORout
03.06.16	Stock1	Item1	3
04.06.16	Stock1	Item1	-2
02.07.16	Stock1	Item2	2
10.07.16	Stock1	Item2	-1
14.07.16	Stock1	Item1	-1

In Pivot table in the end should be:

Year-Month	Item	Balance
06-2016	Item1	1
07-2016	Item2	1
	Item1	0

There also can be Quarter-Year or Week-Year instead of Year-Month.

Please help to solve it. Thank you very much!

[PrashantSangle]

Hi,

in script from dateField create Year Month,Quarter-Week and Week-Year Field

and in front end

take pivot table add

required dimension and expression could be sum(inOrOut)

Note : to create Year Month,Quarter-Week and Week-Year Field see date and time function in help menu.

Regards,

[#3]

Hi! Thanks!

I already created such fields in my Calendar. Everything ok with it.

If I use sum(inORout) and take in pivot table Year-Month in dimension, so it will calculate only values for that period.

Correct calculation will be such:

06-2016 - sum of all values from the start date till the end of June

07-2017 - sum of all values from the start date till the end of July

Using your solution it will calculate only values for that periods, not from the start date.

Thank you, Max, by the way!

[PrashantSangle]

Hi,

In straight table > Expression > select Expression > Accumulation > select full Accumulation.

or in Pivot chart see below thread

Accumulation for Pivot Table

Re: Accumulation in Pivot

Regards

[kaushiknsolanki]

Hi,

What I do in such cases is calculate the Closing stock of every month in script using loop.

Something like below.

Data:

Load Date,Stock,Item,InORout from xyz;

Month:

Load Distinct Monthstart(Date) as Month Resident Data;

Let vCount = noofrows('Month');

For i=0 to $(vCount)-1

     Let vMonth =   Peek('Month',$(i),'Month');  

     MOMStock:

     Load Stock,Item,Sum(InORout) as Inventory,'$(vMonth)' as MonthStart

     Resident Date <= '$(vMonth)';

Next

Drop table Data.Month;

This way you will get the Stock for everymonth

Regards,

Kaushik Solanki

[kumarkp412]

Hi,

Some bit of confusion ,

start date means -

You need to calculate the values from the first row to current rows i.e year field contains the dates from 3-2016 to 7-2016

so for calculating the value for 7-2016 should be as an accumulation of 3-2016 to 7-2016

then in expression use the below expression.

rangesum(above(sum(inORout), 0 , RowNo()))

I hope my understand is correct or let me know .

Thanks

Kumar

[#7]

Thank you!!! It is great

I thought about it. It is right direction. But I have some trouble with it.

For example, as you said I will keep the stock for everymonth. So I can make the app with Month-Year dimension.

What about Week-Year or Quarter-Year?

[kaushiknsolanki]

Hi,

I understand what you are saying about. If your data size is small you can do the week and Qtr on same manner, but I would suggest you should not use that unless its a business need, because that will increase your application size.

Regards,

Kaushik Solanki

[#9]

Thank you, Kumar!

For me this solution is something new. And the result of it is very close for what I'm looking for.

Here, what I made:

Operations:

LOAD * INLINE [

    Дата, Stock, Item, inORout

    03.06.2016, Stock1, Item1, 3

    04.06.2016, Stock1, Item1, -2

    02.07.2016, Stock1, Item2, 2

    10.07.2016, Stock1, Item2, -1

    14.07.2016, Stock1, Item1, -1

];

Also I have a calendar with Month-Year, Week-Year and Quarter-Year.

In my frontend I made a pivot table with dimensions: Month-Year and Item

Expression (as you wrote): rangesum(above(sum(inORout), 0 , RowNo()))

It shows in Month-Year - for June-0, July-1

But when I expand it shows June-Item1-1

July-Item1--1

July-Item2-0

[#10]

Actually I done it for every day. So I have the stock for every day. It works good, but uses a lot of server resourses.

Now, I have a question.

As I said I have a stock for every day.

How can I make in my pivot the next thing. When I use as a dimension the Month-Year, I want to take Stock only the last day of the month (because I have everyday Stock in my qvd). How to make it?

When I use this expression  sum( {$< Date={'$(=max(Date))'}>} Balance) - it shows the result only for the last month, But I want to calculate for every month-year I select.

Thank you!