topic Re: For loop with sum in QlikView

For loop with sum

Anonymous — Fri, 13 Mar 2015 03:37:00 GMT

Hi everyone,

Can someone please help me @calculating the sum(sales) per day with iterations on date.@@

I have 3 fields in a table , start date,duration & sales.

And I calculated the end date.

------------------------------------------------------------------------------------

start date duration sales end date = startdate+duration

-----------------------------------------------------------------------------------

1/1/15 3 1 1/4/15

1/2/15 4 2 1/6/15

1/3/15 6 1 1/9/15

----------------------------------------------------------------------------------------

If i select 1/3/15 it should display all the dates till the end date( 1/3/15 ,1/4/15,1/5/15,1/6/15,1/7/15,1/8/15 ) with the corresponding sum(sales) for each particular day,like below:

startdate 1/1/15 1/2/15 1/3/15 1/4/15 1/5/15 1/6/15 1/7/15 1/8/15

sales 1 1 1

2 2 2 2

1 1 1 1 1 1

sum(sales) 1 3 4 3 3 1 1 1

startdate sales

----------------------------------------

1/3 4

1/4 3

1/5 3

1/6 1

1/7 1

1/8 1

I would really appreciate if someone can help me asap..

Thanks!

Re: For loop with sum

its_anandrjs — Fri, 13 Mar 2015 03:58:54 GMT

Hi,

Try SET Expression for this and implement in the straight table chart. Otherwise make change in the load script or in fact table.

Regards,

Anand

Re: For loop with sum

madhukar_putta — Fri, 13 Mar 2015 04:25:25 GMT

Hi,

PFA. Hope this helps. Thank you,

Regards,

Madhukar

Re: For loop with sum

Anonymous — Fri, 13 Mar 2015 14:33:06 GMT

Thanks a lot Madhukar!! You Rock!!

Re: For loop with sum

Anonymous — Fri, 13 Mar 2015 17:00:03 GMT

Can you please help em writing this piece of code in qlikview

vmin = min(checkindate)

vmax= max(Checkindate)

for dates min(checkindate) to max(check in date)

{

for i=1 to duration ---duration = peek(table,0,duration)

{

v_check_in_date: = vmin+i-1;

v_sales = peek(tabe,i-1,sales);

Temp_table:

Load OR REPLACE

v_check_in_date as v_temp1,

v_sales as v_sales

from v_check_in_date,v_Sales;

}

final_table:

select check_in_date,sum(sales) from temp_table group by check_in_date;

Thanks!

Re: For loop with sum

Sat, 14 Mar 2015 13:34:10 GMT

HI ChinnuKool

OPEN THE QLIKVIEW DOCUMENT AS DATA PRESENT AND AFTER THAT GO TO VARIABLE OVERVIEW TAB AND GIVE Variable Name and FIled As show in the Images

1)Open the Qv Document and go to Setting tab

2)Click on the Settings tab you have a Variable Overview Tab and select the tab

3) Click on the variable tab it opens an dialog box and click add button in box

4)After Click Add Button it appears another dialog box as new Variable.Enter Your Variable name as Vmin click Ok.

5) After That we need to go the variable name and highlight the name after that you enter the field name of the Vmin. Click Ok and You check this in the expression of the data.