topic Re: Show only data from the past 7 days in QlikView

Show only data from the past 7 days

Wed, 25 Nov 2020 16:16:04 GMT

I want to show data from the past 7 days only.

I currently have

sum({<Date={">=$(WeekStart( Today()-7))<$(=today()))"},[Customer] = >}

date([Paid in full date])

date([Invoice date])

) / Count( {<Date={'>=$(WeekStart( Today()-7))'},[Customer] = >}Count)

But the above literally does nothing, its the same as if I didn't put in the time parameters. I tried testing the bottom count(Count)

part to see if my date limitations even worked, and even just the bottom part doesn't change (17 records normally, still 17 after putting the limitation on).

Any ideas?

As you can see, the Average days to pay is the same in both scenarios. The top chart doesn't have the date limitations in the set expression.

Re: Show only data from the past 7 days

antoniotiman — Mon, 19 Jun 2017 15:52:41 GMT

Date={">=$(=WeekStart( Today()-7))........

Re: Show only data from the past 7 days

aarkay29 — Mon, 19 Jun 2017 15:55:07 GMT

Try This

sum({1<Date={">=$(=WeekStart( Today()-7))<$(=today())"},[Customer] = >}

date([Paid in full date])

date([Invoice date])

) / Count( {1<Date={'>=$(=WeekStart( Today()-7))'},[Customer] = >}Count)

Re: Show only data from the past 7 days

Mon, 19 Jun 2017 15:55:27 GMT

sadly didn't work

Also, it has been red underlined this whole time but it says expression OK at the top

Re: Show only data from the past 7 days

Kushal_Chawda — Mon, 19 Jun 2017 15:55:31 GMT

try this

sum({<Date={">=$(=WeekStart( Today()-7))<$(=today())"},[Customer] = >}

date([Paid in full date])

date([Invoice date])

) / Count( {<Date={'>=$(=WeekStart( Today()-7))'},[Customer] = >}Count)

Re: Show only data from the past 7 days

Mon, 19 Jun 2017 15:57:51 GMT

It didn't work says expression OK, but all the code is underlined red (it was before as well), and no difference in the chart.

Re: Show only data from the past 7 days

Mon, 19 Jun 2017 15:58:06 GMT

It didn't work says expression OK, but all the code is underlined red (it was red before as well), and no difference in the chart.

Re: Show only data from the past 7 days

Kushal_Chawda — Mon, 19 Jun 2017 16:00:45 GMT

Please check your Date field name. It's name is really "Date". Looks ike this date field is not available in your model

Re: Show only data from the past 7 days

Mon, 19 Jun 2017 16:03:08 GMT

What do you mean date field? I don't have a date table in my code, I am subtracting the Paid in full date from Invoice date, both which are tables in my code

Re: Show only data from the past 7 days

dwforest — Mon, 19 Jun 2017 16:12:49 GMT

Your set expression needs to reference actual fields...

sum({<[Paid in full date]={">=$(=WeekStart( Today()-7))<$(=today())"},[Customer] = >}

date([Paid in full date])

date([Invoice date])

) / Count( {<[Invoice date]={'>=$(=WeekStart( Today()-7))'},[Customer] = >}Count)

Re: Show only data from the past 7 days

Mon, 19 Jun 2017 16:15:00 GMT

Thank you! I was confused because Qlik also has a date function, so I thought that is why you put Date there, but I guess not.

Re: Show only data from the past 7 days

Kushal_Chawda — Mon, 19 Jun 2017 16:52:20 GMT

This is what I wanted to tell you, but my bad

Re: Show only data from the past 7 days

Mon, 19 Jun 2017 16:55:45 GMT

No worries, thank you