topic Re: IF and set analysis and time in QlikView

IF and set analysis and time

alwayslearning — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a IF statement, which has an expression which sums and converts various time fields.

=num(if((Sum(Log_In_duration)/60)=0,' ' ( (SUM(Time)+SUM(Time2)+ ((sum(Time3)/3600)+sum(Time4)))/60)

/Sum([Log_In_duration]/60)), '##0 %')

In addition to the above formula which works as is, I need the formula to only return date specific data.

I have created a variable titled vcurrentweek from a field "DATE" and was looking to do a SET Analysis for current week and perform the above. However I am getting various errors and hoped someone could help.

Thanks

Re: IF and set analysis and time

Anil_Babu_Samineni — Fri, 05 Jan 2018 17:46:41 GMT

May be this?

=num(if((Sum(Log_In_duration)/60)=0,' ' ( (SUM({<DATE = {$(vcurrentweek)}>} Time)+SUM({<DATE = {$(vcurrentweek)}>} Time2)+ ((sum({<DATE = {$(vcurrentweek)}>} Time3)/3600)+sum({<DATE = {$(vcurrentweek)}>} Time4)))/60)

/Sum({<DATE = {$(vcurrentweek)}>} [Log_In_duration]/60)), '##0 %')

Re: IF and set analysis and time

devarasu07 — Fri, 05 Jan 2018 18:01:14 GMT

Hi,

Another simplified way u can try like below using set analysis

Num(sum( {$<Log_In_duration={"=Sum(Log_In_duration)/60)=0"},Year={"$(=Max(Year))"},Week={$(vcurrentweek)}>} (Time2+(Time3/3600)+(Time4/60))/ (Log_In_duration/60) ),'#,##0%')

Re: IF and set analysis and time

alwayslearning — Fri, 05 Jan 2018 18:29:37 GMT

Thanks Anil,

This works perfectly.

*Note:If anyone uses the code above in the future, there is a , missing before the false statement begins.

Re: IF and set analysis and time

alwayslearning — Fri, 05 Jan 2018 18:30:24 GMT

Thanks Devarasu

Re: IF and set analysis and time

Anil_Babu_Samineni — Sat, 06 Jan 2018 01:47:36 GMT

I am correcting myself

=num(if((Sum(Log_In_duration)/60)=0,' ', ( (SUM({<DATE = {$(vcurrentweek)}>} Time)+SUM({<DATE = {$(vcurrentweek)}>} Time2)+ ((sum({<DATE = {$(vcurrentweek)}>} Time3)/3600)+sum({<DATE = {$(vcurrentweek)}>} Time4)))/60)

/Sum({<DATE = {$(vcurrentweek)}>} [Log_In_duration]/60)), '##0 %')