topic Re: Set Analysis in QlikView

Set Analysis

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

Can you please help me switch the following ifs to set analysis

=if([A_Date]>='04/01/2014',if(IsNull(Price),[G_Price],Price))

Thanks for the help!

Re: Set Analysis

sunny_talwar — Tue, 02 Aug 2016 14:48:18 GMT

Is this an expression or is this a dimension?

Re: Set Analysis

alec1982 — Tue, 02 Aug 2016 14:51:32 GMT

Let me check.. sorry!

Re: Set Analysis

Mark_Little — Tue, 02 Aug 2016 14:51:32 GMT

Hi,

Is this trying to just show a field or you trying to sum or something similar?

Looks like you want if as you are trying to conditional show a field value.

Mark

Re: Set Analysis

sunny_talwar — Tue, 02 Aug 2016 14:56:35 GMT

May be this:

Alt(

Only({<A_Date = {"$(='>=' & Date(MakeDate(2014, 4, 1), 'MM/DD/YYYY'))"}>} G_Price),

Only({<A_Date = {"$(='>=' & Date(MakeDate(2014, 4, 1), 'MM/DD/YYYY'))"}>} Price))

I am assuming your date format is MM/DD/YYYY -> April 1st 2014 (04/01/2015)

Update: Messed up the order

Alt(

Only({<A_Date = {"$(='>=' & Date(MakeDate(2014, 4, 1), 'MM/DD/YYYY'))"}>} Price),

Only({<A_Date = {"$(='>=' & Date(MakeDate(2014, 4, 1), 'MM/DD/YYYY'))"}>} G_Price))

Re: Set Analysis

miguelbraga — Tue, 02 Aug 2016 14:59:59 GMT

Hi there,

Make something this changes:

1) Create a Price master Field in the Script like this:

if(IsNull(Price),[G_Price], Price) as PriceMaster,

2) Create a Flag for the date

if([A_Date]>='04/01/2014', 1, 0) as Flag

3) Finally make your set analysis expression:

=sum({$<Flag = '1'>} PriceMaster)

Hope this way helps you

Regards,

Re: Set Analysis

Anonymous — Tue, 02 Aug 2016 15:03:30 GMT

How about doing the isnull check in the load script such as ,if(IsNull(Price),[G_Price],Price)as newprice

then in expression something like the following

=sum({<[A_Date]={">=04/01/2014"}>}newprice)

Re: Set Analysis

miguelbraga — Tue, 02 Aug 2016 15:11:12 GMT

By the way... this method that I've written is in my humble opinion the most efficient way to implement what you're trying to do, considering the best perfomance.

PS: If you want you can mantain your expression using a variable like this:

Let vStartDate = '04/01/2014';

And then use it in the creation of Flag field like this:

if([A_Date]>=$(vStartDate), 1, 0) as Flag,

Re: Set Analysis

sunny_talwar — Tue, 02 Aug 2016 15:25:10 GMT

Can I offer an improvement here:

If Price is truly null

Alt(Price, [G_Price]) as PriceMaster,

or this

If(Len(Trim(Price)) > 0, Price, G_Price) as PriceMaster