topic Re: Set expression not working as intended in QlikView

Set expression not working as intended

Wed, 25 Nov 2020 16:16:04 GMT

I have the following expression in a chart, which members here told me would only show data from the past 7 weeks

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

date([Paid in full date])

date([Invoice date])

) / Count( {<[Paid in full date]={">=$(=Date(Today()-7))<=$(=Date(Today()-1))"},[Customer] = >}Count)

However, I want to test with data in the last 6 days, so I replaced the 7 with a 6, but nothing appeared.

Here are my dates

Any ideas?

Re: Set expression not working as intended

vishsaggi — Tue, 20 Jun 2017 15:08:42 GMT

May be try this:

One small suggestions do not ever use the native QV functions names as your column names. Just want to confirm the one colored in red below is that your Field name Count can you rename that field QV thinks that as a native function. I would not use any native function names as my field names.

Try this:

= sum({<[Paid in full date]={">=$(=Date(Today()-7, , 'MM/DD/YY'))<=$(=Date(Today()-1, 'MM/DD/YY'))"},[Customer]= >} date([Paid in full date]) - date([Invoice date]))/

Count({<[Paid in full date]={">=$(=Date(Today()-7, , 'MM/DD/YY'))<=$(=Date(Today()-1, , 'MM/DD/YY'))"},[Customer]= >} Count)

Re: Set expression not working as intended

sunny_talwar — Tue, 20 Jun 2017 15:17:19 GMT

so I replaced the 7 with a 6, but nothing appeared.

What do you mean here? what did not appear? Can you share a sample?

Re: Set expression not working as intended

Tue, 20 Jun 2017 15:22:08 GMT

Okay I have changed my Count table to Payment_count.

I implemented the change you suggested, but now it looks the same as my chart with

sum({<[Customer] = >}

date([Paid in full date])

date([Invoice date])

)/ Count( {< [Customer] = >}Payment_count)

which is the original without any date constraints

Re: Set expression not working as intended

vishsaggi — Tue, 20 Jun 2017 15:28:18 GMT

OK, so you still have the issue? With your first expression?

Re: Set expression not working as intended

Tue, 20 Jun 2017 15:32:12 GMT

Right, it now seems to show all data regardless of date.

Is there something wrong with my syntax? Are the Date and Today supposed to be blue?

Re: Set expression not working as intended

Tue, 20 Jun 2017 15:33:19 GMT

Using a line chart, it says no data to display. But there should be, because as you can see in my date range, there is data from 5 to 6 days ago.

Re: Set expression not working as intended

sunny_talwar — Tue, 20 Jun 2017 15:34:46 GMT

Dates are visible, yes. But do you have count (or new Payment_count) associated with all dates? May be the rest of the 6 days have null count?

Re: Set expression not working as intended

vishsaggi — Tue, 20 Jun 2017 15:37:43 GMT

You have an extra comma in the Date. It should be like

">=$(=Date(Today() -5, 'MM/DD/YY'))

My bad.

May be use this?

= sum({<[Paid in full date]={">=$(=Date(Today()-7, 'MM/DD/YY'))<=$(=Date(Today()-1, 'MM/DD/YY'))"},[Customer]= >} date([Paid in full date]) - date([Invoice date]))/

Count({<[Paid in full date]={">=$(=Date(Today()-7, 'MM/DD/YY'))<=$(=Date(Today()-1, 'MM/DD/YY'))"},[Customer]= >} Payment_Count)

Re: Set expression not working as intended

Tue, 20 Jun 2017 15:38:51 GMT

I just checked, and Payment Count is associated with everything. Payment count is just a count of all the records in my data file, so it is associated with literally everything in my data set

Re: Set expression not working as intended

Tue, 20 Jun 2017 15:42:36 GMT

Ah. I've come to the conclusion that these are both the same lol

With the MM/DD/YY or without it, it still results in "no data to display" on the line chart

Re: Set expression not working as intended

sunny_talwar — Tue, 20 Jun 2017 15:43:36 GMT

Mock up or a sample?

Re: Set expression not working as intended

vishsaggi — Tue, 20 Jun 2017 15:45:15 GMT

Can you share a sample if possible?

Re: Set expression not working as intended

Tue, 20 Jun 2017 15:47:06 GMT

Here is QVD. Can't share Customers, thats in another file, but this has dates and count.

Re: Set expression not working as intended

Tue, 20 Jun 2017 15:48:45 GMT

This has dates and count, but does not include Customers, as I can't share that.

Re: Set expression not working as intended

sunny_talwar — Tue, 20 Jun 2017 15:56:06 GMT

Paid in full date = Invoice date between 6/14/2017 and 6/19/2017 making the numerator = 0

Re: Set expression not working as intended

Tue, 20 Jun 2017 15:59:01 GMT

I see. So what does this imply? Is there a fix for this? If the difference is 0 between the dates, I want it to stay that way, so are you saying my chart and expression is correct as -is?

Re: Set expression not working as intended

sunny_talwar — Tue, 20 Jun 2017 16:00:13 GMT

I don't know what the expectation is, but your expression is working and the only reason you see 0 is because your numerator is 0.

Re: Set expression not working as intended

Tue, 20 Jun 2017 16:01:31 GMT

For example, if there are 2 records, the first was paid in 2 days, and the other was paid on the same day aka 0.

So in my Average days to pay chart, I want it to graph 1 day. Is this possible? I believe now it just ignores all 0's and only calculates it if it is 1 or above

Re: Set expression not working as intended

sunny_talwar — Tue, 20 Jun 2017 16:09:38 GMT

I think that's what it will do....