Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Periods of dates in pivot table

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

11 Replies

Re: Periods of dates in pivot table

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

Re: Periods of dates in pivot table

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!

Re: Periods of dates in pivot table

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.

Re: Periods of dates in pivot table

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
Contributor II

Re: Periods of dates in pivot table

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

Re: Periods of dates in pivot table

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?

Re: Periods of dates in pivot table

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

Not applicable

Re: Periods of dates in pivot table

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

Re: Periods of dates in pivot table

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!

Community Browser