Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Help needed with expressions in the table

Hello,

I'm running into an issue , when trying to create table view with calculated rows and calculated columns.

Here is an example:

QV Question.JPG

As you know, green area is not an issue, since it can be done through Calendar dim. Red one is the one I'm struggling with since Change and Change,% need to calculated on the fly based on Selections. Am I missing something?

I have tried to surf web but found close to nothing, if you happened to know good resources, please share.

Looking forward for your inputs.

Dmitry

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Hello!

There are some questions about your issue.

Is year a dimension?

If the answer Yes then: What a behavior do you expect for situation when users select more than 2 years. I mean columns Change and Change%.

If we are talking about only 2 selected years then we can use following approach:

1. Create table for "dimensions"

t1:

LOAD * Inline [

Dim

Avg Mbr Count

Cost Per Member

Cost Per Contract

];

2. Add 4 expressions

For example:

Lable: =min(Year)

Definision:

if(Dim='Avg Mbr Count',count({$<Year={$(=min(Year))}>} MemberName),

  if(Dim='Cost Per Member', sum({$<Year={$(=min(Year))}>} CostValue)/count({$<Year={$(=min(Year))}>} MemberName),

     if(Dim='Cost Per Member', sum({$<Year={$(=min(Year))}>} CostValue)/count({$<Year={$(=min(Year))}>} Contract))

)

)

etc...

View solution in original post

2 Replies
Highlighted
Specialist
Specialist

Hello!

There are some questions about your issue.

Is year a dimension?

If the answer Yes then: What a behavior do you expect for situation when users select more than 2 years. I mean columns Change and Change%.

If we are talking about only 2 selected years then we can use following approach:

1. Create table for "dimensions"

t1:

LOAD * Inline [

Dim

Avg Mbr Count

Cost Per Member

Cost Per Contract

];

2. Add 4 expressions

For example:

Lable: =min(Year)

Definision:

if(Dim='Avg Mbr Count',count({$<Year={$(=min(Year))}>} MemberName),

  if(Dim='Cost Per Member', sum({$<Year={$(=min(Year))}>} CostValue)/count({$<Year={$(=min(Year))}>} MemberName),

     if(Dim='Cost Per Member', sum({$<Year={$(=min(Year))}>} CostValue)/count({$<Year={$(=min(Year))}>} Contract))

)

)

etc...

View solution in original post

Highlighted
Not applicable

Hello. Thank you for your quick reply, Sergey. Provided clue is right on the money.

Exact behaviour and details were to much to explain but your answer is a huge help. A+