Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excel scorecard to QlikView - Advice?

Hey, I need some advice, how this can be handled in QlikView?

"What is best practices in this case?" Have you made any similar cases?

The customer has old Excel file what he uses as a scorecard. He wants make similar QlikView scorecard.

I can get all data from SQL database and other Excel files. I have created qvd files from these sources.

Here is example Scorecard:

scorecard.PNG.png

As you can see, the scorecard consists of a range of different data from multiple sources.

Different unit types, headers, etc...

And you all know that this very easy for Excel but pain to QlikView.

Any ideas, how I can make similar QlikView scorecard?

What do you recommend? How could I do this smartly?

Here my thoughts:

I try break data multiple pieces and I create multiple pivot tables?

(problems with linking data)

I create one pivot table and I add multiple expressions?

(poor to maintain)

3 Replies
Greg_Williams
Employee
Employee

I would use the QlikView Transformation Wizard > available when loading xls file > select Next (not the typical) Finish > looks like will take several passes to grab the exact data you want (one section at a time).

Gysbert_Wassenaar

It's easiest to use multiple pivot tables and/or straight charts. It looks like you need four or five tables.


talk is cheap, supply exceeds demand
pieterkup
Partner - Contributor II
Partner - Contributor II

Hi Jack,

There's basically two ways to do this. The first is to change the Straight table over to a Horizontal Table. The Expression Labels will then be listed on the left, in stead of at the top. (on the Presentation Tab - Tick box for Horizontal). The downside of this is you can only have one column at a time.

The second option would be to define a calculated dimension with the Valueloop function. Valueloop(0,3)

Then, on the Expressions, you'll use the Pick function to define different headings for the different lines. Pick(Valueloop(0,3), 'Turnover', 'Taxes', 'Turnover %')

Then, in a second Expression, you'll do the same as above, but replace the headings with the specific expressions to calculate the values. eg Pick(Valueloop(0,3),num(sum([Turnover]),'#,##0'),num(sum([Taxes]),'#,##0'),num(sum([Turnover])/sum([Taxes]),'#0.0%')). This will then be the first values.

You can then set the 'Dimension' field to hidden. This will build the Scorecard that you have above.

I had to do the same thing at a client of ours and the second option was the easiest way to build the scorecards.

I hope you find this useful.