Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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 |