Date - 1 in set expression

[pascos88]

Hi,

I need to count going 1 day back :

count({<date_lgn_timestamp= {date_lgn_timestamp}-1 >} lgn_timestamp)

How I can use this ?

Thanks

[rubenmarin]

Hi Pasquale, maybe with:

count({<date_lgn_timestamp= {'$(=timestamp(date_lgn_timestamp-1))'} >} lgn_timestamp)

But still can fail, timestamp format returned in $(=) should be the same as the are stored in the date_lgn_timestamp field.

[pascos88]

Hi Ruben thanks for the answer,

but doesn' t work fine..

Please find attached a picture hope that could helps

Thanks

[rubenmarin]

Hi Pasquale, it won't work if there is a dimension with date, set analysis is calculated before the table, so the same date it's applied to all rows, also it will need a TOTAL qualifier to ignore date dimension.

If you want to retrieve the value of the row below you can use Below() function:

Below(count(date_lgn_timestamp))

[rubenmarin]

Note that gaps between dates can return unwanted results, check if it can work with your data.

[pascos88]

Hi Ruben, thank was really helpful,

now the problem is that for each column I have to trace 1 day back.. for the first column Below(count(date_lgn_timestamp)) could be fine... but what happen now for the second and 3 column?

Thanks for your help

[rubenmarin]

The 2nd parameter of below can be used to jump more than one row:

2nd column: Below(count(date_lgn_timestamp), 2)

3rd column: Below(count(date_lgn_timestamp), 3)

https://help.qlik.com/es-ES/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/InterReco...

[justinphamvn]

Hi,

Pls try this

count({<date_lgn_timestamp= {">=$(=Date(Max(date_lgn_timestamp) - 1))<=$(=Date(Max(date_lgn_timestamp) - 1))"} >} lgn_timestamp)

Hope this helps,

Justin.

[pascos88]

Hi Justin,

I cannot see the whole expression

[justinphamvn]

Hi,

I have attached a file in txt

Regards,

Justin.