Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Based on Another Field (similar to Prior Period but can't use a calculation)

Hi everyone,

I'm trying to use set analysis to create a pivot table that shows financial figures for the currently selected period and previous period. I know this question has been asked before, however all of the forum submissions were able to do this via a calculation (e.g. Year - 1). In my application, this isn't possible because the period is a text value and uses business rules to define the previous period. Thus, I have a lookup table which lists all of the periods and what their previous period are (maintained by the user in Excel actually).

For example, I have the following table which contains my data:

Period

Measure

Value

Q2 FY09 Dec OL

Opex

1000

Q2 FY09 Apr OL

Opex

5000



And in another table I have:

Period

PreviousPeriod

Q2 FY09 Apr OL

Q2 FY09 Dec OL



My pivot table needs to look like this:

Measure

Q2 FY09 Apr OL

Q2 FY09 Dec OL

Opex

5000

1000

.....

....

....



The first expression is easy because if someone selects a period then you just do a sum.

The second expression needs to be the previous period but do a lookup of what that is based on another column.

I've tried multiple set analysis expressions, none of which work. Oddly, if I hardcode the value it works.

Here's what I've tried:

sum({$<Period= {$(=only(PreviousPeriod))}>} Value) - didn't work, returned null
sum({$ <Period=PreviousPeriod>} Value) - didn't work, returned 0
sum({$ <Period={PreviousPeriod}>} Value) - didn't work, returned 0
sum({1 <Period=PreviousPeriod>} Value) - didn't work, returned 0

But this works:
sum({$ <Period={"Q2 FY09 Dec OL"}>} Value)

Thanks for the help in advance.

Regards,
Steve

1 Solution

Accepted Solutions
Master
Master

Hi Steve,

As you can see when you hard code the value, you use quotes around the value which is necessary in this case since the text value contains spaces.

If you look at the expressions, for example this:

sum({$<Period= {$(=only(PreviousPeriod))}>} Value) - didn't work, returned null

This will work if the Period didn't have any spaces in it. Instead you have to add quotes around the dollar-sign expansion to get it to resolve in the same manner as the hard coded expression:

sum({$<Period= {"$(=only(PreviousPeriod))"}>} Value)

This should do the trick.

View solution in original post

4 Replies
Not applicable

the only thing I could come up with is


sum({1<Period={'$(=FieldValue('PreviousPeriod',1))'}>} Value)


But this is static and depends on the load order. I'd recommend you add an additional field to your data so you can define your set and know where up & down is.

Not applicable

I'd recommend at least you structure your data as


Data:
load * inline [
Period, Measure, Value
Q2 FY09 Dec OL, Opex, 1000
Q2 FY09 Apr OL, Opex, 5000
];
Periods:
load * inline [
Period, Num
Q2 FY09 Apr OL,1
Q2 FY09 Dec OL,2
];


so you can move around with something like

=sum(FieldValue('Value',Num))

Master
Master

Hi Steve,

As you can see when you hard code the value, you use quotes around the value which is necessary in this case since the text value contains spaces.

If you look at the expressions, for example this:

sum({$<Period= {$(=only(PreviousPeriod))}>} Value) - didn't work, returned null

This will work if the Period didn't have any spaces in it. Instead you have to add quotes around the dollar-sign expansion to get it to resolve in the same manner as the hard coded expression:

sum({$<Period= {"$(=only(PreviousPeriod))"}>} Value)

This should do the trick.

View solution in original post

Not applicable

Thanks Johannes, that worked! Quick question - is there a way to make this work without using the only() function? Knowing that my lookup contains spaces and w/out creating a key of somesort, can you put double quotes around the field or something to that effect? I tried various solutions but could not get that to work. The reason I'm asking is because I need to keep this expressions as simple as possible since the customer will be maintaining them. Using only() or some other function will add to the maintenance headache.

Thanks!