topic Re: Excelfile Taskforce in QlikView

Excelfile Taskforce

pacoli2013 — Mon, 13 Mar 2017 11:33:51 GMT

The missing excelfile for my Set Analysis Problem

See QVD:

In the tab Dashboard Sales Report is a table chart and a blockchart.

The blockchart is a copy of the table only design different.

In the Table there are some Actual andBudget columns with expressions like:

= if (company = 1, Sum(ECOwert), if (company = 2, Sum(ESBwert), (Sum(ECOwert) + Sum(ESBwert))))

= if (company = 1, if(MarkerWorkFree = 'A', (if(Anteil < 1, (PlanECOpT) * Anteil, PlanECOpT)), 0),

if (company = 2, if(MarkerWorkFree = 'A', PlanESBpT, 0),

if(MarkerWorkFree = 'A', ( (if(Anteil < 1, (PlanECOpT) * Anteil, PlanECOpT)) + PlanESBpT), 0)))

These IF- statemants I wish to replace with set-analysis expressions if possible.

Perhaps I first have te redesign my Excel file, I don't know. I'm open for suggestions

regards

Court

Re: Excelfile Taskforce

Anonymous — Mon, 13 Mar 2017 12:03:40 GMT

May I ask, why you want to change it to set analysis?

Nested IFs in 6 levels could end in a very confusing statement... I don't even know if it is possible for your example.

Perhaps it would be easier to calculate that in the script?

Re: Excelfile Taskforce

marcus_sommer — Mon, 13 Mar 2017 16:04:30 GMT

I think there are several things which could be (better) done different. For example by the first:

pick(wildmatch(Company, 'Company1', 'Company2', '*'),

Sum(ECOwert),

Sum(ESBwert),

rangesum(Sum(ECOwert), Sum(ESBwert)))

By the second expression might be a similar solution possible. It's not quite clear for me if the query on MarkerWorkFree is really needed - and if could it be simplified by multiplying the flag against the result by changing the flag from A to 1 and F to 0. A bit similar seems to me Anteil - could Anteil be greater than 1 - if not you might use: PlanECOpT * alt(Anteil, 1).

- Marcus

Re: Excelfile Taskforce

pacoli2013 — Tue, 21 Mar 2017 15:45:27 GMT

Hello Marcus,

Thanks for your solution, it works. I use a Flag also. Anteil is skipped in this table, I make some calculations in the script and create a new table on a new sheet. This is less complex and for users easier to understand

But creating a new table for Anteil forced I to exclude the influence of some filters so I rebuild your solution a little (hope you don't mind):

= (pick(wildmatch(Company, 'Company1', 'Company2', '*'),

Sum({$ < Year = {$(= max(Year))}, Mountinggroup=, Mounting= > } ECOValue),

Sum({$ < Year = {$(= max(Year))}, Mountinggroup=, Mounting= > } ESBValue),

rangesum(Sum({$ < Year = {$(= max(Year))}, Mountinggroup=, Mounting= > } ECOValue), Sum({$ < Year = {$(= max(Year))}, Mountinggroup=, Mounting= > } ESBValue))))

This works but I was asking myself/thinking, is there a better solution?

Regards

Court

Re: Excelfile Taskforce

marcus_sommer — Wed, 22 Mar 2017 06:09:07 GMT

I think on the chart-level won't be much possibilities to improve these logic but with changes on the datamodel it could be simplified and speed up - by generating a single Value-field which contained the right value regarding to each company. But this could mean a whole change of your datamodel and if it yet worked like expected I wouldn't change it only to optimize this expression.

- Marcus

Re: Excelfile Taskforce

pacoli2013 — Wed, 22 Mar 2017 07:03:25 GMT

Thanks Marcus for your help

I think you are right. Most values are generated in a DWH and for changing my datamodel, there should be changes made there. That's to much work for one datamodel or Qlikview Document

Court