topic Re: AsOfTable for WEEK period in QlikView

AsOfTable for WEEK period

Thu, 15 Oct 2015 12:22:24 GMT

Hello everone,

I am doing my best but I cant figure out, how to cummulate data to weekly stats.

I prefer month/year format even for weeks - MM/YYYY.

Here is my code, but it is nonsense, I am just trying.

[Data2]:

LOAD

WeekStart(date([Data.Datum], 'MM/YYYY')) AS Period1,

MonthName([Data.Datum]) AS Month

Resident Data;

// Temporary table with list of Period values

PeriodTbl:

LOAD

DATE(fieldvalue('Period1',recno()), 'MM/YYYY') AS Period

AUTOGENERATE fieldvaluecount('Period1');

AsOfPeriodTable:

//Load every month as current month into AsOf table

LOAD

Period AS [AsOfPeriod]

,'Current' AS PeriodType

,Period AS Period1

,Year(Period) AS Year

,Month(Period) AS Month

,Week(Period) AS Week

,Week(Period)&'/'&Year(Period) AS [Week/Year]

,IF(Month(Period)<4, Year(Period)-1, Year(Period)) AS [Year fiskal]

RESIDENT PeriodTbl;

CONCATENATE (AsOfPeriodTable)

//Load RollingWeeks into AsOf table

LOAD

Period AS [AsOfPeriod]

,'RollingWeeks' AS PeriodType

//,date(AddMonths(Period,1-iterno()),'MM/YYYY') AS Period1

,DATE(Period + iterNo()*7, 'MM/YYYY') AS Period1

//this was ment to increase period into next week, then 14 days to second and so

,IF(Month(Period)<4, Year(Period)-1, Year(Period)) AS [Year fiskal]

,Year(Period) AS Rok

,Month(Period) AS Month

,Week(Period) AS Week

,Week(Period)&'/'&Year(Period) AS [Week/Year]

RESIDENT PeriodTbl

WHILE iterno() <= 70; //we have like 470 week, but I divided it by 7 as a count of days

/*INNER JOIN (AsOfPeriodTable)

LOAD Period as Period1

RESIDENT PeriodTbl;*/

DROP TABLE PeriodTbl;

DROp Table Data;

After all, another problem will be the relationship with calendar.

But now, I will be very thankful just for rolled data to week since beginning of evidence (01/04/2007).

Re: AsOfTable for WEEK period

jolivares — Thu, 15 Oct 2015 12:59:21 GMT

Trying to undertand your problem, first try to post some data in order to explain better your case.

But any way, as you have data with a date and a week to separate thar you just have to accumulate in an expression in your QV model. Don't stress your script trying to do something that you can do easily with an expression.

Re: AsOfTable for WEEK period

Thu, 15 Oct 2015 13:50:28 GMT

I think if somebody knows how AsOf table works, It may not be problem for him to show me how to cummulate data in weeks.

My data ale stock transfers. So I have data for every day, but I need them to roll up to selected year, month, week. Cos only cummulative data show real prices of stock supplies.

And not only that, I need it to be working alongside selections.

I have something like this in expression:

it says: ignore selected year, month and week, and cummulate prices to date at $(vObratkaMesicKS)' variable.

SUM({<id_typZbozi={1}, [Kalendar.Rok fiskal]=, Kalendar.Mesic=, Kalendar.Tyden=, [Data.Datum]={'<=$(vObratkaMesicKS)'}>}[Data.Skladova cena celkem]))

It's working so good. But when it comes to making graphs, ignored selections of year, month and week, keep graph witth all years shown even Ive chosen one and even more, I have to use rangesum to collect information from previous graph columns.

So no, I havent figured out the way how to do it design. Maybe you will...

However, my AsOfTable for Month cummulation seems to work fine, I just need data rolled into weeks.

But Im open to any other ideas then AsOfTable .

This is bar chart which I want and I get it only by using AsOf table:

Selected is PROJECT - OBP, Week/Year on axis- also from AsOf table, but its the week of beginning of the month and dates are not coordinated with MasterCalendar yet 😕

As you see, number at the end 40/2015 is the same as number written up in the expression above. I just need those freaking weeks

Re: AsOfTable for WEEK period

Thu, 22 Oct 2015 05:57:59 GMT

Hey guys, any ideas?

I am really desperate about summing stock prices and number of goods and this is the only step for me to finish and public the application.

Please help!