Qlik Community

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
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: 

Przechwytywanie.PNG

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

View solution in original post

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)

View solution in original post

Highlighted
Contributor II
Contributor II

Yeah thank you