Need Set Analysis help

[#1]

Hi,

I have the following table structure.

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

I've tried with;

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

What is the correct syntax for this using Set Analysis?

Best regards,

Jonas

[Anonymous]

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?

[petter]

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 !

[NickHoff]

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

[#5]

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

[petter]

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!