3 Replies Latest reply: Nov 14, 2014 5:26 AM by Pieter Kuperus RSS

    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)

        • Re: Excel scorecard to QlikView - Advice?
          Greg Williams

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

          • Re: Excel scorecard to QlikView - Advice?
            gysbert wassenaar

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

            • Re: Excel scorecard to QlikView - Advice?
              Pieter Kuperus

              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.