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: 
Not applicable

Periods of dates in pivot table

Hi guys!

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

For example:

DateStockIteminORout
03.06.16Stock1Item13
04.06.16Stock1Item1-2
02.07.16Stock1Item22
10.07.16Stock1Item2-1
14.07.16Stock1Item1-1

In Pivot table in the end should be:

Year-MonthItemBalance
06-2016Item11

07-2016

Item21
Item10

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

Please help to solve it. Thank you very much!

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

All you need to do is create a flag in Script to identify the Last day. Something like this.

Load *,If(Date = Monthend(Date),1,0) as Monthend_Flag From Xyz;

Now go to pivot and put expression as

Sum({<Monthend_Flag = {"1"}>}Stock)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

11 Replies
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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
kumarkp412
Creator II
Creator II

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

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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

Not applicable
Author

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!