topic Re: Possible to use Formulas for PowerPoint Graphs ? in Qlik NPrinting

Possible to use Formulas for PowerPoint Graphs ?

C_Johan_V_Ekman — Wed, 11 Sep 2019 05:48:57 GMT

Hi,

I have created simple "normal" formulas that works fine when the tag is placed "openly" on the powerpoint (Like a "KPI").
Exmaple value 120. Displayed on preview fine as a KPI.

However, when trying to use the tag inside a bar chart (Edit data on chart -> Opens excel -> Add tag where value of bar should be) to generate a bar it lays "dormant" / "inactivated" when previeing the powerpoint. On the previewed PPT I open the bar chart by editing data to find that the tag is there but have not generated the value of 120. Just the tag in text.

Is it not possible to use formula "tags" in edit data (excels) in powerpoint graphs?
Using table tags and cell tags works fine but those are of course reliable on created objects in QLikview / Sense. Would like to not be dependant on created objects in apps in this case hence using formulas.

Possible - no yes why? 😃

Re: Possible to use Formulas for PowerPoint Graphs ?

Frank_S — Wed, 11 Sep 2019 18:02:30 GMT

Hi @C_Johan_V_Ekman

Can you show some screenshots of following to demonstrate your steps to reproduce and a mock up of desired results:

Your template editor and what you mean by "when trying to use the tag inside a bar chart (Edit data on chart -> Opens excel -> Add tag where value of bar should be)"
What you are experiencing (snapshots of any error or other that might help use to understand your requirement better)
What your expectations for output are (mockup if possible)

Are you trying to create a MS pptx chart with QV or QS data? Something else?

Re: Possible to use Formulas for PowerPoint Graphs ?

C_Johan_V_Ekman — Thu, 12 Sep 2019 05:15:14 GMT

Hi @Frank_S !

Thanks for your reply. I have attached a screenshot when in edit template mode.

1. You see in the formulas the one red marked called "Passengers" I am trying to use.
2. This formula can be seen as well in the edit formula window. Using a Qlikview App "Operations".
3. "Passengers" are also used directly on the report. See red marked upper right corner.
When previewing it generates the correct number. ex: 45 000
4. The goal / ambition was to use this tag /forumla "Passengers" in a PPT chart. See red marked chart and the "edit data" window open where the tag is used.
5. The chart should generate a bar with the value of 45 000 like the KPI in preview. But it is not "activated" or generating a number. No errors and no value. Just the tag <Passengers> is seen in preview mode.

Trying any other formulas in the bar chart is producing the same "nothingness". Just the tag name in text when previewing. When using "tables" or "cells" "tags" and not formulas into bar chart works fine. But In this case I want to use the formula for simplicity of NOT creating an underlying object in a Qlikview app.

So, in short. the Formula works fine on the report but not when used to generate chart values. Expected outcome should be a bar with 45 000 in value.

The app connection works fine. The filtering on all levels for the report is minimal and works fine.

Thanks for your suport

Re: Possible to use Formulas for PowerPoint Graphs ?

Frank_S — Thu, 12 Sep 2019 15:17:39 GMT

HI @C_Johan_V_Ekman

NPrinting Designer is not really the best place to build charts. It is designed to best leverage charts built in QVWs and QVFs.

NP designer won't be able to fluidly utilize formulas in the method you are proposing.

I would recommend that you insert a chart with the formula information you require into your source QVW or QVF application, reload your NP metadata to the QVW or QVF. You will then be able to leverage the formulas you required as chart fields effectively.

Other's might have other solutions for you here in the community conversation...but as far as best practices, I would go with building charts using formulas that you need as columns in your source QVW or QVF.

Kind regards...

Re: Possible to use Formulas for PowerPoint Graphs ?

pwagner — Tue, 11 Jan 2022 13:06:01 GMT

Hello @Frank_S

I tried your idea:

"I would recommend that you insert a chart with the formula information you require into your source QVW or QVF application, reload your NP metadata to the QVW or QVF. You will then be able to leverage the formulas you required as chart fields effectively."

I put a table into an excel of an power point chart.

But it does not work.

Have I misunderstood your idea?

@C_Johan_V_Ekman

What was your solution in this topic?

Thank you.

Best regards, Patrick

Re: Possible to use Formulas for PowerPoint Graphs ?

Ruggero_Piccoli — Tue, 11 Jan 2022 13:41:49 GMT

Hi,

You have to drag and drop table tags in the PowerPoint template directly.

Best Regards,

Ruggero

Re: Possible to use Formulas for PowerPoint Graphs ?

pwagner — Tue, 11 Jan 2022 14:53:47 GMT

So I cannot add tables, columns, formulas or cells into an excel of a power point chart, correct?

Best regards, Patrick

Re: Possible to use Formulas for PowerPoint Graphs ?

Lech_Miszkiewicz — Wed, 12 Jan 2022 00:05:14 GMT

Hi @pwagner

As Ruggero said - you can only drag table column tags into PowerPoint (Excel entry for chart) and this process is described in help document.

https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/PowerPointReports/Chart-Native-Tables.htm

So for the purpose shown on your screenshot in this topic you would have to create a single table in Qlik using either ValueList() or island field as dimension and measure in such as Pick(RowNo().....1 st measure, 2nd measure, 3rd measure) or similar concept.

Cells, Variables, Levels, Pages, images and Formulas are not supported there!

cheers

Lech

Re: Possible to use Formulas for PowerPoint Graphs ?

pwagner — Wed, 12 Jan 2022 07:18:20 GMT

Thank you @Lech_Miszkiewicz

A single table with one dimension and one measure, correct?

Re: Possible to use Formulas for PowerPoint Graphs ?

pwagner — Wed, 12 Jan 2022 08:20:53 GMT

@Lech_Miszkiewicz
I created the table in Qlik Sense.

With the dimension and the pick-measure.

Unfortunately it does not work in the excel for the power point chart.

What should I change that it works?

Best regards, Patrick

Re: Possible to use Formulas for PowerPoint Graphs ?

Lech_Miszkiewicz — Wed, 12 Jan 2022 10:03:51 GMT

Hi @pwagner

you have to follow the process...

in your screenshot your excel has 2 sheets. Obviously you (or someone else) have modified it.

When inserting PPT chart excel opens up always with Sheet1 and special placeholder in which you should insert table column tags. That excel looks by default like this:

and whole process .GIF

If you have done modification to this excel file then it is likely the cause of your issue.

cheers

Re: Possible to use Formulas for PowerPoint Graphs ?

pwagner — Thu, 20 Jan 2022 08:51:02 GMT

Thank you @Lech_Miszkiewicz

Understood.

We found another solution for this request.

Regards, Patrick