Formula in qlik sense

[pascos88]

Hello,

I need your helps to create a formula.

I have this table

day                               count_lgn         1_Day_back               2 Days_back               3 Days_back

10.05.2018                         100                        45                              88                              11

10.04.2018                         88                           11                             22                              65

Now let's assume that day is my column called 'DAY'

Count login in qlik sense is my count(user_login)

now in 1_Day back column I have to calculate the count(user_login) for 1_day_back means 10.04.2018 but just considering the user_login that are counted in 10.05.2018.

-->for the day 10.05.2018 my formula per 1_Day_back should count:

count (user_login) where day = (day-1) and user_login in (user_login of 10.05.2018)

-->for the day 10.04.2018 my formula per 1_Day_back should count:

count (user_login) where day = (day-1) and user_login in (user_login of 10.05.2018)

-->for the day 10.05.2018 my formula per 2_Day_back should count:

count (user_login) where day = (day-2) and user_login in (user_login of 10.05.2018)

Thaks for any helps,

Best

[juraj_misina]

You can try your luck with the P() function in set analysis:

1_Day_back = Count( {$<DAY={‘$(=Max(DAY)-1)’}, user_login= P({1<DAY={‘$(=Max(DAY))’}>} user_login)>} user_login)

2_Days_back = Count( {$<DAY={‘$(=Max(DAY)-2)’}, user_login= P({1<DAY={‘$(=Max(DAY))’}>} user_login)>} user_login)

3_Days_back = Count( {$<DAY={‘$(=Max(DAY)-3)’}, user_login= P({1<DAY={‘$(=Max(DAY))’}>} user_login)>} user_login)

More info: https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetA...

[pascos88]

Hi Juraj, thanks for your answer..

I am not sure about the second part of set analysis:  user_login= P({1<DAY={‘$(=Max(DAY))’}>} user_login

I mean the frist part is fine, what you try to do with the second?

Thanks for any clarifications..

Best

[juraj_misina]

Hi Pasquale,

the second part selects only those user_logins which are associated with the Max(DAY) date (10.05.2018 in your example). So this should do exactly what you need it to do -> calculate logins for previous days, but only for those users who logged in on a selected day.

Hope this helps

Juraj