topic Re: Date - 1 in set expression in QlikView

Date - 1 in set expression

pascos88 — Thu, 27 Sep 2018 15:37:59 GMT

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

Re: Date - 1 in set expression

rubenmarin — Thu, 27 Sep 2018 15:44:39 GMT

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.

Re: Date - 1 in set expression

pascos88 — Fri, 28 Sep 2018 06:40:10 GMT

Hi Ruben thanks for the answer,

but doesn' t work fine..

Please find attached a picture hope that could helps

Thanks

Re: Date - 1 in set expression

rubenmarin — Fri, 28 Sep 2018 06:44:22 GMT

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

Re: Date - 1 in set expression

rubenmarin — Fri, 28 Sep 2018 06:52:23 GMT

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

Re: Date - 1 in set expression

pascos88 — Fri, 28 Sep 2018 06:55:55 GMT

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

Re: Date - 1 in set expression

rubenmarin — Fri, 28 Sep 2018 06:59:38 GMT

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/InterRecordFunctions/below.ht…

Re: Date - 1 in set expression

justinphamvn — Fri, 28 Sep 2018 07:01:58 GMT

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.

Re: Date - 1 in set expression

pascos88 — Fri, 28 Sep 2018 07:06:23 GMT

Hi Justin,

I cannot see the whole expression

Re: Date - 1 in set expression

justinphamvn — Fri, 28 Sep 2018 07:29:20 GMT

Hi,

I have attached a file in txt

Regards,

Justin.