Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2017-09-17 08:33 PM

Question on Load script with while statement

Hello Everyone,

I try to load project budget to each month between project start date and project end date.I have question on my loading script:

Here is loading script for daily budget. This one working fine.

LOAD [Proj ID] as GoalProj,
Year([Proj Start Dt]+iterno()-1) as GoalYear,
Month([Proj Start Dt]+iterno()-1) as GoalMonth,
num(Month([Proj Start Dt]+iterno()-1)) as GoalMonthNo,
Date([Proj Start Dt]+iterno()-1) as GoalDate,
[Proj Tot Value]/([Proj End Dt]-[Proj Start Dt]+1) as DailyGoalAmount
while ([Proj Start Dt]+iterno()-1) <= [Proj End Dt]  ;

Here is monthly budget loading stript. This one is not working because the while statement is not right.

LOAD [Proj ID] as GoalProj,
Year([Proj Start Dt]+iterno()-1) as GoalYear,
Month([Proj Start Dt]+iterno()-1) as GoalMonth,
num(Month([Proj Start Dt]+iterno()-1)) as GoalMonthNo,
//Date([Proj Start Dt]+iterno()-1) as GoalDate,
[Proj Tot Value]/((year([Proj End Dt])*12+Month([Proj End Dt]))-(Year([Proj Start Dt])*12+month([Proj Start Dt]))+1 ) as MonthlyGoalAmount
while (num(Year([Proj Start Dt])*12+month([Proj Start Dt]))+iterno()-1) <= num(year([Proj End Dt])*12+Month([Proj End Dt])) ;

Can you help find why this monthly loading script is not working?

Thank you guys very much!

Best regards!

559 Views
0 Likes
1 Solution

Accepted Solutions
marcus_sommer
MVP
MVP
‎2017-09-19 03:40 AM

In response to

Like above mentioned I would quite probably go with a daily budget-value and aggregate the needed periods over them within the UI. I didn't test a while-loop to create monthly budget-values but I created two versions of them with aggregation-loads. Maybe it's helpful for you to find the most suitable approach for your use-case:

DailyBudget:

load *, MonthName(monthstart(Date)) as MonthName;

load

    *, date(From + iterno() -1) as Date, [Proj Tot Value] / (To - From) as DailyBudget

    while From + iterno() - 1 <= To;

load

    [Project ID], [Proj Name],    [Proj Tot Value], [MonthlyGoalAmount], [Proj Start Dt], [Proj End Dt],

    date(date#([Proj Start Dt], 'MM/DD/YYYY')) as From, date(date#([Proj End Dt], 'MM/DD/YYYY')) as To

inline [

Project ID    Proj Name    Proj Start Dt    Proj End Dt    Proj Tot Value    MonthlyGoalAmount    GoalMonth    GoalYear

BJ1-0001    Work TRANSER    12/12/2016    09/30/2017    2612383    261238.3    Dec    2016

BJ1-0002    Work TRANSER    12/12/2016    09/30/2017    221581    22158.1    Dec    2016

BJ1-0003    Work TRANSER    12/12/2016    12/31/2017    1208    92.92307692    Dec    2016

BJ1-0004    Work TRANSER    12/12/2016    12/31/2017    206204    15861.84615    Dec    2016

BJ1-0005    Work TRANSER    12/12/2016    12/31/2017    729686    56129.69231    Dec    2016

BJ1-0006    Work TRANSER    12/12/2016    12/31/2017    39541    3041.615385    Dec    2016

BJ1-0007    Work TRANSER    12/12/2016    12/31/2017    5031    387    Dec    2016

BJ1-0008    Work TRANSER    12/12/2016    12/31/2017    40009    3077.615385    Dec    2016

BJ1-0009    Work TRANSER    12/12/2016    12/31/2017    6699    515.3076923    Dec    2016

BJ1-0010    Work TRANSER    12/12/2016    12/31/2017    3256    250.4615385    Dec    2016

BJ1-0011    Work TRANSER    12/12/2016    12/31/2017    73066    5620.461538    Dec    2016

BJ1-0012    Work TRANSER    12/12/2016    12/31/2017    12291    945.4615385    Dec    2016

BJ1-0013    Work TRANSER    12/12/2016    12/31/2017    197628    15202.15385    Dec    2016

BJ1-0014    Work TRANSER    12/12/2016    12/31/2017    186244    14326.46154    Dec    2016

BJ1-0015    Work TRANSER    12/12/2016    12/31/2017    157816    12139.69231    Dec    2016

BJ1-0016    Work TRANSER    12/12/2016    12/31/2017    64216    4939.692308    Dec    2016

BJ1-0017    Work TRANSER    12/12/2016    12/31/2017    51059    3927.615385    Dec    2016

] (txt, delimiter is \t);

MonthlyBudget1:

load [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date)))) as YearMonth1, sum(DailyBudget) as MonthlyBudget1

resident DailyBudget group by [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date))));

left join (MonthlyBudget1)

load [Project ID], avg([Proj Tot Value]) / count(distinct MonthName) as MonthlyBudget2

resident DailyBudget group by [Project ID];

- Marcus

View solution in original post

DistributeBudgetToDaysAndMonth.qvw
430 Views
0 Likes
6 Replies
marcus_sommer
MVP
MVP
‎2017-09-18 08:37 AM

I think the while-condition should be look something like this:

while addmonths(monthstart([Proj Start Dt]), iterno() - 1)) < monthend([Proj End Dt]);

maybe with a bit adjusting to really needed dates which might be the exact date or any kind of monthstart/monthend.

Beside them I'm not sure if I would use such a monthly budget-table - why not using the table on the daily-level and then aggregating them on any needed period within the charts?

- Marcus

430 Views
Not applicable
‎2017-09-18 10:01 AM
Author

In response to marcus_sommer

Thank you very much - Marcus.

Your solution looks very very smart. But It looks only run one time when I test this script. The result is like this:

       

Project IDProj NameProj Start DtProj End DtProj Tot ValueMonthlyGoalAmountGoalMonthGoalYear
BJ1-0001Work TRANSER12/12/201609/30/20172612383261238.3Dec2016
BJ1-0002Work TRANSER12/12/201609/30/201722158122158.1Dec2016
BJ1-0003Work TRANSER12/12/201612/31/2017120892.92307692Dec2016
BJ1-0004Work TRANSER12/12/201612/31/201720620415861.84615Dec2016
BJ1-0005Work TRANSER12/12/201612/31/201772968656129.69231Dec2016
BJ1-0006Work TRANSER12/12/201612/31/2017395413041.615385Dec2016
BJ1-0007Work TRANSER12/12/201612/31/20175031387Dec2016
BJ1-0008Work TRANSER12/12/201612/31/2017400093077.615385Dec2016
BJ1-0009Work TRANSER12/12/201612/31/20176699515.3076923Dec2016
BJ1-0010Work TRANSER12/12/201612/31/20173256250.4615385Dec2016
BJ1-0011Work TRANSER12/12/201612/31/2017730665620.461538Dec2016
BJ1-0012Work TRANSER12/12/201612/31/201712291945.4615385Dec2016
BJ1-0013Work TRANSER12/12/201612/31/201719762815202.15385Dec2016
BJ1-0014Work TRANSER12/12/201612/31/201718624414326.46154Dec2016
BJ1-0015Work TRANSER12/12/201612/31/201715781612139.69231Dec2016
BJ1-0016Work TRANSER12/12/201612/31/2017642164939.692308Dec2016
BJ1-0017Work TRANSER12/12/201612/31/2017510593927.615385Dec2016

The reason I want to generate monthly goal is my revenue actual is by month.

Thank you very much for your inputs. Appreciate.

Best regards.

430 Views
0 Likes
marcus_sommer
MVP
MVP
‎2017-09-18 10:30 AM

In response to

Monthly data could be treated as daily data just by converting the Year and Month into a date. For example with something like: makedate(Year, Month, 1) which made the handling quite easy. We use this approach since many years and concatenate the budget data just to the sales data whereby the comparing of both values is done with a monthly forecast of sales and the budget is already a monthly value.

- Marcus

430 Views
Not applicable
‎2017-09-18 10:50 AM
Author

In response to marcus_sommer

Marcus,

You are awesome with so many good solutions on my question. We have monthly revenue actual. But for project budget is by project period based on project start date and project end date. So I try to average this budget by months and compare to actual.

For  the loading script, the daily script is working and can get budget for each day. I just cannot understand why the monthly script is only run once when load.

Thank you again - Marcus. Your reply is greatly appreciated!

430 Views
0 Likes
marcus_sommer
MVP
MVP
‎2017-09-19 03:40 AM

In response to

Like above mentioned I would quite probably go with a daily budget-value and aggregate the needed periods over them within the UI. I didn't test a while-loop to create monthly budget-values but I created two versions of them with aggregation-loads. Maybe it's helpful for you to find the most suitable approach for your use-case:

DailyBudget:

load *, MonthName(monthstart(Date)) as MonthName;

load

    *, date(From + iterno() -1) as Date, [Proj Tot Value] / (To - From) as DailyBudget

    while From + iterno() - 1 <= To;

load

    [Project ID], [Proj Name],    [Proj Tot Value], [MonthlyGoalAmount], [Proj Start Dt], [Proj End Dt],

    date(date#([Proj Start Dt], 'MM/DD/YYYY')) as From, date(date#([Proj End Dt], 'MM/DD/YYYY')) as To

inline [

Project ID    Proj Name    Proj Start Dt    Proj End Dt    Proj Tot Value    MonthlyGoalAmount    GoalMonth    GoalYear

BJ1-0001    Work TRANSER    12/12/2016    09/30/2017    2612383    261238.3    Dec    2016

BJ1-0002    Work TRANSER    12/12/2016    09/30/2017    221581    22158.1    Dec    2016

BJ1-0003    Work TRANSER    12/12/2016    12/31/2017    1208    92.92307692    Dec    2016

BJ1-0004    Work TRANSER    12/12/2016    12/31/2017    206204    15861.84615    Dec    2016

BJ1-0005    Work TRANSER    12/12/2016    12/31/2017    729686    56129.69231    Dec    2016

BJ1-0006    Work TRANSER    12/12/2016    12/31/2017    39541    3041.615385    Dec    2016

BJ1-0007    Work TRANSER    12/12/2016    12/31/2017    5031    387    Dec    2016

BJ1-0008    Work TRANSER    12/12/2016    12/31/2017    40009    3077.615385    Dec    2016

BJ1-0009    Work TRANSER    12/12/2016    12/31/2017    6699    515.3076923    Dec    2016

BJ1-0010    Work TRANSER    12/12/2016    12/31/2017    3256    250.4615385    Dec    2016

BJ1-0011    Work TRANSER    12/12/2016    12/31/2017    73066    5620.461538    Dec    2016

BJ1-0012    Work TRANSER    12/12/2016    12/31/2017    12291    945.4615385    Dec    2016

BJ1-0013    Work TRANSER    12/12/2016    12/31/2017    197628    15202.15385    Dec    2016

BJ1-0014    Work TRANSER    12/12/2016    12/31/2017    186244    14326.46154    Dec    2016

BJ1-0015    Work TRANSER    12/12/2016    12/31/2017    157816    12139.69231    Dec    2016

BJ1-0016    Work TRANSER    12/12/2016    12/31/2017    64216    4939.692308    Dec    2016

BJ1-0017    Work TRANSER    12/12/2016    12/31/2017    51059    3927.615385    Dec    2016

] (txt, delimiter is \t);

MonthlyBudget1:

load [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date)))) as YearMonth1, sum(DailyBudget) as MonthlyBudget1

resident DailyBudget group by [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date))));

left join (MonthlyBudget1)

load [Project ID], avg([Proj Tot Value]) / count(distinct MonthName) as MonthlyBudget2

resident DailyBudget group by [Project ID];

- Marcus

DistributeBudgetToDaysAndMonth.qvw
431 Views
0 Likes
Not applicable
‎2017-09-19 09:52 AM
Author

In response to marcus_sommer

Dear Marcus,

Your solution looks great for my question. MonthlyBudget2 is looks right. I am going to implement your script to my app.

Thank you very much on your smart and nice solution. Wish you all the best.

Dust

430 Views
0 Likes