# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Contributor II

## SUM Product in Qlik with if condition with reference to column

Hej Community,

I stuck with a problem to find exactly the same equation in qlik as in excel

Here is my excel formula

=SUMPRODUCT ((\$B\$2:\$B\$622792>F2)*(\$B\$2:\$B\$622792<F3)*(d=\$G\$1))

My expected result do the same in qlik:

What I did:

1) Create a column like in F:

Dates:
Load
Date(Date,'D/M/YYYY h:mm') as DateId,
Date;
Load
AddMonths(today()+0.25,0) - recno() AS Date
Autogenerate (AddMonths(today()+0.25,0) - vLastYear);// MakeDate(2012,01,01));

table1:
LOAD *,

Timestamp([DateId]+(IterNo()-19)/3, 'D.M.YYYY h:mm' ) as [DataTime]

//Time((IterNo()-1)/4) as [Time of Creation]

Resident Dates
While IterNo()<=24;
Drop Table Dates;

2) I don not know how to select

1 row, then 2 row, and so on and make the calculation, more info how it should be in calculation.xlsx

Thank you for your help

Labels (2)

• ### qlik view

1 Solution

Accepted Solutions
Highlighted

What is calculation of these? Is that simple as below?

If(Hour(b)>=0 and Hour(b)<=6, Date(b,'DD-MMM-YYYY') & ' ' & '6:00',
If(Hour(b)>6 and Hour(b)<=14, Date(b,'DD-MMM-YYYY') & ' ' & '14:00',
If(Hour(b)>14 and Hour(b)<=22, Date(b,'DD-MMM-YYYY') & ' ' & '22:00'))) as Hour_Flag,

 12/2/2018 6:00 12/2/2018 14:00 12/2/2018 22:00 12/3/2018 6:00 12/3/2018 14:00
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
2 Replies
Highlighted

What is calculation of these? Is that simple as below?

If(Hour(b)>=0 and Hour(b)<=6, Date(b,'DD-MMM-YYYY') & ' ' & '6:00',
If(Hour(b)>6 and Hour(b)<=14, Date(b,'DD-MMM-YYYY') & ' ' & '14:00',
If(Hour(b)>14 and Hour(b)<=22, Date(b,'DD-MMM-YYYY') & ' ' & '22:00'))) as Hour_Flag,

 12/2/2018 6:00 12/2/2018 14:00 12/2/2018 22:00 12/3/2018 6:00 12/3/2018 14:00
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor II
Yeah thank you