topic Set analysis in QlikView

Set analysis

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We need the data between the date ranges.Below is the logic which is not getting results.Please provide the right expresiion.

=sum({$<AuditDate >={'9/1/2016'}<={'9/3/2016'}>}PostedSales)

Thanks..

Re: Set analysis

sunny_talwar — Tue, 04 Oct 2016 12:57:05 GMT

May be try this:

=Sum({$<AuditDate = {">='9/1/2016'<='9/3/2016'}>} PostedSales)

Re: Set analysis

nareshthavidishetty — Tue, 04 Oct 2016 13:14:48 GMT

Hi Sunny,

It's showing no results..

Thanks..

Re: Set analysis

sunny_talwar — Tue, 04 Oct 2016 13:15:49 GMT

How is AuditDate formatted?

Re: Set analysis

trdandamudi — Tue, 04 Oct 2016 13:17:13 GMT

May be try this:

=Sum({$<AuditDate = {">=9/1/2016<=9/3/2016"}>} Sales)

Re: Set analysis

sunny_talwar — Tue, 04 Oct 2016 13:18:19 GMT

Yes, I should have not added the single quotes

Re: Set analysis

trdandamudi — Tue, 04 Oct 2016 13:20:06 GMT

Sunny,

Is it because the double quote ( " ) is not closed :

=Sum({$<AuditDate = {">='9/1/2016'<='9/3/2016'}>} PostedSales)

May be like below ?

=Sum({$<AuditDate = {">='9/1/2016'<='9/3/2016' "}>} PostedSales)

Re: Set analysis

sunny_talwar — Tue, 04 Oct 2016 13:22:04 GMT

I still don't think it would work, for my solution to work, in addition to adding ending double quote, I will need dollar sign expansion:

=Sum({$<AuditDate = {"$(='>=9/1/2016<=9/3/2016')"}>} PostedSales)

but why go through all that hassle, when we can just do what you proposed

Re: Set analysis

sunny_talwar — Tue, 04 Oct 2016 13:23:16 GMT

Or may be it would work without dollar sign expansion also, I am confused and will have to test it out. But I guess I usually don't even use hard-coded values.

Re: Set analysis

trdandamudi — Tue, 04 Oct 2016 13:27:11 GMT

You are right, adding double quote not worked... but I also tried dollar sign expansion as below but no luck.. I thought this will work....

=Sum({$<AuditDate = {"$(='>=9/1/2016<=9/3/2016')"}>} PostedSales)

Re: Set analysis

trdandamudi — Tue, 04 Oct 2016 13:29:22 GMT

Same here... It is very rare I use hard coded values. Yes, it works without dollar sign expansion...

Re: Set analysis

sunny_talwar — Tue, 04 Oct 2016 13:31:46 GMT

Oh really, with dollar sign expansion also not working? Would you be able to share where you tested this?

Re: Set analysis

johanlindell — Tue, 04 Oct 2016 13:35:50 GMT

I think the syntax should be like this:

Sum({$<AuditDate = {">=9/1/2016<=9/3/2016"}>} PostedSales)

Or if you are using dollarexpansion for the last 3 months

Sum({$<AuditDate = {">$(=Date (AddMonths (Today(), -3)))<=$(=Today ())"}>} PostedSales)

Re: Set analysis

sunny_talwar — Tue, 04 Oct 2016 13:40:42 GMT

Here are the test results

1) =Sum({$<AuditDate = {"$(='>=9/1/2016<=9/3/2016')"}>} PostedSales)

2) =Sum({$<AuditDate = {'>=9/1/2016<=9/3/2016'}>} PostedSales)

3) =Sum({$<AuditDate = {">='9/1/2016'<='9/3/2016' "}>} PostedSales)

1 and 2 worked, but 3 did not work

Re: Set analysis

miguelbraga — Tue, 04 Oct 2016 13:45:15 GMT

Hey there,

Try to add a variable as a vStartDate and another as a vEndDate. Then you can do like this:

Sum({$<AuditDate = {">=$(=vStartDate)<=$(=vEndDate)"}>} PostedSales)