Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

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)

Tags (3)
3 Replies
Employee
Employee

Re: Excel scorecard to QlikView - Advice?

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).

Highlighted
MVP & Luminary
MVP & Luminary

Re: Excel scorecard to QlikView - Advice?

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
Partner
Partner

Re: Excel scorecard to QlikView - Advice?

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.