topic Re: Need Set Analysis help in QlikView

Need Set Analysis help

Wed, 28 Jan 2015 16:18:56 GMT

Hi,

I have the following table structure.

I want to summarize the "Working Hours" where Calender Date = Working Day

I've tried with;

sum({$<[Working Day]={"=[Calender Date]"}>}[Working Hours])
sum({$<counter={"=[Working Day]=[Calender Date]"}>}[Working Hours])

What is the correct syntax for this using Set Analysis?

Best regards,

Jonas

Re: Need Set Analysis help

Anonymous — Wed, 28 Jan 2015 18:32:01 GMT

Not enough information.

If you want to use it in a chart, probably the correct syntax for this is to not use Set Analysis. Can you upload the application?

Re: Need Set Analysis help

petter — Wed, 28 Jan 2015 19:41:12 GMT

Although the documentation of QlikView says that you can write a valid Set Expressions the way you do it doesn't calculate... A bug it seems that Qlik hasn't bothered to fix.

Luckily there is a way of calculating it by writing it this way:

You will need to have a field in the same table as the field you are summing over which is a unique row ID.

I have called this ID and put it in the working_hours table. Then you can do a search comparing Date with [Working Day]. It is important that you have this ID however to get QlikView to calculate. It can be autogenerated...

I have attached a "raw hack" of a QlikView App that show it with a real example - if you need a working example.

Good luck !

Re: Need Set Analysis help

NickHoff — Wed, 28 Jan 2015 19:45:01 GMT

SUM({$<PrimaryKey={"=(Date)=[Working Days]"}>}[Working Hours])

Re: Need Set Analysis help

Fri, 30 Jan 2015 14:20:59 GMT

Thanks for your answer!

I got your example to work in my original application but it doesn't give me the result I was looking for. It summarizes the Working Hours but only when selected one specific date.

Let me explain in more detail what I'm trying to accomplish.

I have two tables;

Table 1 contains [Workload Hours] per [Workload Required Date]

Table 2 contains [Capacity Hours] per [Capacity Day]

I want to show in a diagram [Workload Hours] and [Capacity Hours] per day.

(Blue line at the bottom is capacity but for some reason not working wright now).

This can be accomplished by the following IF expression but this takes a long time to execute;

sum(if([Calender Date]=[Workload Required Date], [Workload Hours])

sum(if([Calender Date]=[Capacity Day], [Capacity Hours])

Can I solve this using set analysis or have a approached the problem the wrong way?

Thanks,

Jonas

Re: Need Set Analysis help

petter — Fri, 30 Jan 2015 14:53:31 GMT

Well your disconnected fiscal_calendar (a data island / date island) probably contribute to your performance problems. You can have multiple calenders in your data model. You can even have a master calendar og what Henric Cronström calls a canonical calendar in addition to multiple specific calendars. Have a look at his excellent explanation of this approach in the QlikView Design Blog: http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

With

One Canoncial calendar
One WorkLoad Calendar
One WorkingHours Calendar

You will get the maxium flexiblity to achieve your necessary selections for differnt kinds of calculations and analysis.

Henric Cronström's blog article explain the necessary steps to achieve this.

Good luck!