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

AsOfTable for WEEK period

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).

3 Replies
jolivares
Valued Contributor

Re: AsOfTable for WEEK period

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.

Not applicable

Re: AsOfTable for WEEK period

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

Not applicable

Re: AsOfTable for WEEK period

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!

Community Browser