topic Set Analysis using Left function in QlikView

Set Analysis using Left function

zagzebski — Thu, 05 Sep 2013 20:14:41 GMT

My data sctucture:

Product	Invoiced Year	Invoiced YearMonth	Paid YearMonth	Amount
A	2012	201201	201202	25
A	2012	201202	201205	50
A	2012	201303	201206	75
A	2012	201204	201204	100
A	2013	201301	201306	125
A	2013	201302	201304	150
A	2013	201303	201303	175
A	2013	201304	201305	200

Current Object Expressions:

Current Year Amount:

=Sum ({$<[Incurred Year] = {$(=Max([Incurred Year]))}>}[Amount])

Prior Year Amount:

'=Sum ({$<[Incurred Year] = {$(=Max([Incurred Year]-1))}>}[Amount])

These expression work fine but I want to add the additonal filter for Paid Year just like I have it for Incurred Year in the expressions. However there is no Paid Year in the data so I am wondering how you can get the left 4 characters from the Paid YearMonth and then use it like is being used for the Incurred Year in the expressions above.

Re: Set Analysis using Left function

jpapador — Thu, 05 Sep 2013 20:25:54 GMT

Why not just make a paid year field in the script. Left([Paid YearMonth], 4) as PaidYear

Re: Set Analysis using Left function

zagzebski — Thu, 05 Sep 2013 20:29:39 GMT

Unfortunately I am not always in control of the script - as in this case. Also I seem to come across this need at various times when I want to use a just portion of a field in my set analysis in combination with max/min.

Re: Set Analysis using Left function

whiteline — Thu, 05 Sep 2013 20:30:40 GMT

Hi.

You have to choose some field to make selection, assume [Paid YearMonth].

Then you could use search capabilities of set analysis (selection by expression):

=Sum ({$<[Paid YearMonth] = {"=Left( [Paid YearMonth] ,4)=$(=Left(Max( [Paid YearMonth])), 4)"}>}[Amount])

Re: Set Analysis using Left function

zagzebski — Thu, 05 Sep 2013 20:41:28 GMT

Thanks!

So I understand - why aren't we able to just eliminate the middle part and have something like this?:

<[Paid YearMonth] =$(=Left(Max( [Paid YearMonth])), 4)"}>}

Instead of:

<[Paid YearMonth] = {"=Left( [Paid YearMonth] ,4)=$(=Left(Max( [Paid YearMonth])), 4)"}>}

I know there is a good reason. Thanks so much in advance!

Re: Set Analysis using Left function

whiteline — Thu, 05 Sep 2013 20:56:02 GMT

Because Paid YearMonth contains values like YYYYMM, while left(...,4) returns only YYYY.

The suggested set expression filters those Paid YearMonth values for which the expression between quotes is true.

Re: Set Analysis using Left function

zagzebski — Tue, 10 Sep 2013 12:50:21 GMT

Hi -

I thought this expression was working but when I set it alone in an expression it returns 0 for every line (no mater what I select on the Paid YearMonth list box):

Sum ({$<[Paid YearMonth] = {"=Left( [Paid YearMonth] ,4)=$(=Left(Max( [Paid YearMonth])),4)"}>}[DHP Liability Amount])