Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Expression in row and column in a straight table

Hi all,

I have to do a table like this one :


Current QuarterLast Quarter
VariationIndicator
Revenue

%
Revenue stores

%
Revenue Internet

%

Revenue is the sum of field REVENUE, Revenue stores is the sum of Revenue where SOURCE='STORE', the variation is the difference between Quarter and Quarter-1, Indicator is an arrow (using qmem pictures).

The problem, is that I should have an expression in row and column.

My idea was to use Valuelist as dimension like this :

Dim : Valuelist('Revenue','Revenue stores','Revenue Internet') <- set in the variable vList

And then to test the valuelist for the expression in each row like this :

if($(vList)='Revenue',

sum({<QUARTER={$(=MAX(QUARTER))}>}REVENUE),

if($(vList)='Revenue stores',

sum({<SOURCE={'STORES'},QUARTER={$(=MAX(QUARTER))}>}REVENUE),

sum({<SOURCE={'INTERNET'},QUARTER={$(=MAX(QUARTER))}>}REVENUE)))

For the moment, this solution is working. But it is very complicated and I think hard to maintain.

So, I am there to ask for your expertise and experience, is it a good solution ?

Would you have used another method ?

Should I do something in the script ?

Thanks,

Ben

1 Solution

Accepted Solutions
Not applicable

Re: Expression in row and column in a straight table

Well, I would load the expressions (pref. from excel) in script and use parameters.

Let vRevenue = sum({<QUARTER={$(=MAX(QUARTER))}>}REVENUE),

Let vRevenue_split = sum({<SOURCE={$1},QUARTER={$(=MAX(QUARTER))}>}REVENUE)

This replaces the expressions in table:

$(vRevenue)

$(vRevenue_split('STORES'))

$(vRevenue_split('INTERNET'))

4 Replies
Not applicable

Re: Expression in row and column in a straight table

I'd create a calculated dimension:

=ValueLoop(1,3,1)

and then the expressions:

Headline:

pick(valueloop(1,3,1),

'Revenue',

'Revenue Stores',

'Revenue Internet'

)

Current Quarter:

pick(valueloop(1,3,1),

sum({<QUARTER={$(=MAX(QUARTER))}>}REVENUE),

sum({<SOURCE={'STORES'},QUARTER={$(=MAX(QUARTER))}>}REVENUE),

sum({<SOURCE={'INTERNET'},QUARTER={$(=MAX(QUARTER))}>}REVENUE)

)

and so on...

Not applicable

Re: Expression in row and column in a straight table

Thanks Thomas,

Your solution is working fine !

So, according to you, I had nothing to do in the script ?

Not applicable

Re: Expression in row and column in a straight table

Well, I would load the expressions (pref. from excel) in script and use parameters.

Let vRevenue = sum({<QUARTER={$(=MAX(QUARTER))}>}REVENUE),

Let vRevenue_split = sum({<SOURCE={$1},QUARTER={$(=MAX(QUARTER))}>}REVENUE)

This replaces the expressions in table:

$(vRevenue)

$(vRevenue_split('STORES'))

$(vRevenue_split('INTERNET'))

Not applicable

Re: Expression in row and column in a straight table

Thanks Thomas,

Your solution is working well.

However, if someone has another idea do not hesitate to submit it. I am interested to know if everyone would have solved the problem like this.


Community Browser