topic Re: Set Analysis -problem in QlikView

Set Analysis -problem

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

Hi,

Is the order of the dimensions important when performing a calculated in Set Analysis?

In the example attached, I am trying to count the ID's where source='online' and SaleDate between the Variables- vMinTimeStamp and vMaxTimeStamp and ignore the dimension-source

I have defined the variebles-

Let vMinTimeStamp =Timestamp(min(SaleDate),'YYYY-MM-DD hh:mm:ss')

Let vMaxTimeStamp= Timestamp(max(SaleDate),'YYYY-MM-DD hh:mm:ss')

in Set Analysis I have tried:

=count({$<SaleDate = {">=$(vMinTimeStamp)<= $(vMaxTimeStamp)"},source ={'online'},source = >}ID)

but it's not working, becouse I want to ignore the dimension "source"

In addition, I noticed that if I write in the beginning of the Set Analysis: source =

like this:

=count({$<source = ,SaleDate = {">=$(vMinTimeStamp)<= $(vMaxTimeStamp)"},source ={'online'}>}ID)

the result is changing, why?? is the order in set analysis important?

My expectation should be displayed like this:

**if the user selects the sorce='email' - the result will not change,

the result will change only when the user selects SaleDate

Appreciates any help!

Re: Set Analysis -problem

PrashantSangle — Tue, 25 Jul 2017 11:46:59 GMT

Hi,

It is showing as per logic you build.

On selection of email from source field your min value is changing because of that your end output is changing.

for that you have to nullify source = in variable definition.

Regards,

Prashant

Re: Set Analysis -problem

sunny_talwar — Tue, 25 Jul 2017 11:55:18 GMT

I think dreamer4‌ is right. Because your date change based on selection in source field, the value of the count also changes. I used this for the variables

vMaxTimeStamp

=Timestamp(max({<source>}SaleDate),'YYYY-MM-DD hh:mm:ss')

vMinTimeStamp

=Timestamp(min({<source>}SaleDate),'YYYY-MM-DD hh:mm:ss')

Re: Set Analysis -problem

ttal7878 — Tue, 25 Jul 2017 11:55:33 GMT

What do you mean by "nullify source = in variable definition"

Can you show this in the example I attached?

Re: Set Analysis -problem

ttal7878 — Tue, 25 Jul 2017 12:03:18 GMT

Wow you are amazing !!

But,

In my original model, I have several Text Object , some of which I want to ignore the source dimension and some not,

How can i deal with this, when the user selects a date range?

Re: Set Analysis -problem

PrashantSangle — Tue, 25 Jul 2017 12:05:01 GMT

update your variable definition as sunny suggested. nullify means ignoring your selection

min({<source=>}date).

regards,

Re: Set Analysis -problem

Miguel_Angel_Baeyens — Tue, 25 Jul 2017 12:12:45 GMT

Your initial expression used "source" twice, whenver you want to avoid a field value to be considered, use the same field with an equal and nothing on the right, like:

=count({$<source=, SaleDate = {">=$(vMinTimeStamp)<= $(vMaxTimeStamp)"} >} ID)

The filter for SaleDate will work but regardless what has been selected in field "source"

Re: Set Analysis -problem

sunny_talwar — Tue, 25 Jul 2017 12:13:56 GMT

May be create two set of variables... one when you ignore selection in source and other where you don't

vMaxTimeStampIgnoreSource

=Timestamp(max({<source>}SaleDate),'YYYY-MM-DD hh:mm:ss')

vMinTimeStampIgnoreSource

=Timestamp(min({<source>}SaleDate),'YYYY-MM-DD hh:mm:ss')

vMaxTimeStamp

=Timestamp(max(SaleDate),'YYYY-MM-DD hh:mm:ss')

vMinTimeStamp

=Timestamp(min(SaleDate),'YYYY-MM-DD hh:mm:ss')

Re: Set Analysis -problem

Miguel_Angel_Baeyens — Tue, 25 Jul 2017 12:20:59 GMT

Maybe you can use this?

The Basics about Dates, Date Ranges and Set Analysis

Re: Set Analysis -problem

ttal7878 — Tue, 25 Jul 2017 12:46:30 GMT

OK Sunny,

i can create two set of variables

but in the Input box in which the user selects date range, which variables I should put ?

Re: Set Analysis -problem

sunny_talwar — Tue, 25 Jul 2017 13:57:47 GMT

Right now vMinTimeStamp and vMaxTimeStamp are both expressions and are not driven by user inputs (at least directly).... are you looking for them to be driven by user inputs in an input box object?

Re: Set Analysis -problem

ttal7878 — Wed, 26 Jul 2017 05:52:51 GMT

Yes

Re: Set Analysis -problem

PrashantSangle — Wed, 26 Jul 2017 05:58:54 GMT

Hi,

use ignored source field in variable.

use vMaxTimeStampIgnoreSource and vMinTimeStampIgnoreSource variable as suggested by stalwar1‌

Regards,

Re: Set Analysis -problem

ttal7878 — Wed, 26 Jul 2017 06:49:48 GMT

As I have written to stalwar1‌,

In my original model, I have several Text Object , some of which I want to ignore the source dimension and some not,

How can i deal with this, when the user selects a date range?

(I want the variables to be driven by user inputs in an input box object)

Re: Set Analysis -problem

PrashantSangle — Wed, 26 Jul 2017 07:09:02 GMT

It is not possible in 1 variable.

Create 4 variable as suggested by sunny and use that in object as per your requirement.

Regards,

Re: Set Analysis -problem

ttal7878 — Wed, 26 Jul 2017 07:39:36 GMT

Thank you ALL!!