I have some complex reporting I'm trying to build, and at a total loss at several parts of it. I'm totally new to Qlik Sense and, frankly, am finding it horribly slow and frustrating, spending hours on what should be simple. Trying to break things down into smaller chunks, one of the difficulties I'm having is trying to get data summarized by month and year of a field (ultimately something to be calculated based on a few factors, but let's leave that out for now). 

What I need is something like this (minus some decent formatting due to HTML limitations): 

Note the tracking date across the top summarizing by year but in the current year breaking it down by month.  So far, I can't even figure out how to make Qlik show data by the month if the month is not represented in the data. Given the other built-in drill-down capabilities of Qlik, I had thought this would be something built into the tool as a commonly needed tool... summarize by year, then click on a year to expand to see the months. Am I just missing something obvious and built-in?

Taking a little further, is there some training/tutorial on how to build something that works like the above? Perhaps I'm going about it from the wrong angle at the start. 

Thanks!

Hello Folks,

I have a datamodel that has three tables.  On the UI side, I have a straight table showing the data.  What I would like to do is count the number of times a value in Field A does not appear in Field B.

For example, the data set below:

AllowedTerminals:
LOAD * Inline
[
    'Bill To Terminal', 'Order Bill To Id'
    'GSP', 'IBM',
    'CLT', 'IBM',
]
;

Orders:
LOAD * Inline
[
    'Order #', 'Order Bill To Id'
    '18CA7B', 'IBM'
    '63D95E', 'IBM',
]
;


Assets:
LOAD * Inline
[
    'Order #', 'Tractor', 'Tractor Terminal'
    '18CA7B', '123 Freightliner', 'GSP',
    '63D95E', '900 Mack', 'LAX'
]
;

EXIT Script
;

In the example above, the tractor used for Order #18CA7B is terminaled at GSP.  GSP exists in the [Bill To Terminal] field that is linked to the [Order Bill To Id] of IBM.

However, Order #63D95E used a tractor terminaled at LAX, and LAX is not in the [Bill To Terminal] list.

I'm trying to get something like the following where the field CrossTerminalCount reflects the number of times this has happened.

The only solution that I can think of revolves around using the P() function, but every time my mind has gone in that direction, I've always had to backtrack.

Any help is greatly appreciated.

On Tuesday, Palm Beach County Commissioners were briefed on the new COVID-19 dashboard the state will be migrating to soon, known as "Qlik."

Learn more.

Hi guys!

Following question because i think i go crazy.

I have a table with "reclamations" and this "reclamations" have different Date "Positions"

Some are notificated = we was informed that there is a problem, some are picked up from us from the customer, some are arrived our stock.

then there are timestamps when the customer is informed about costs and when for him its ok the date when gives us the ok.

start repair date, delivery etc.

All i want to have is a gantt with a timleine where i can mke this visible but i dont check it.

example for a line:

3 days from notification to pickup, 7 days from pickup to arrive our company, 15 days from inform the customer to his answer, 8 days for repair / production, 4 days from delivery stock to shippment.....

Has anybody an idea how i can solve this problem?

Thank you very much!

Hello I have this variable 

Set f_GroupScore =
'If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore1,
1,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore2,
2,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore3,
3,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore4,
4,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore5,
5,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore0,
1,
Null()
)
)
)
)
)
)';

I am trying to load it as a field but in the table it isn't expanding.  I loaded it as:

AHT:
Load
KEY_IdDate,
REPORT_GROUP,
GroupScore1,
GroupScore2,
GroupScore3,
GroupScore4,
GroupScore5,
GroupScore0,
ACDCALLS,
ACDTIME,
HOLDTIME,
ACWTIME,
TOTALACDCALLS,
ACDCALLS/TOTALACDCALLS as Weight,
'$(f_GroupScore)'*(ACDCALLS/TOTALACDCALLS) as Weight1
resident Temp2_AHT
;

I want to expand it in the load editor so I can then filter certain values from showing up in the grids but instead of evaluating the expression it is only returning the string of the expression.  I've tried 'set', and 'let', I've tried inputing '=' in the parantheses.  Not sure what I'm missing is it not evaluating because it is an if statement?  Any help would be appreciated.  

Thanks, 

I have ID, Date, MonthYear and Status as fields.

1. I have to count all the IDs that have more than one Running or Pending or Complete Status

2. I also have to count IDs that have more than one Running or Pending or Complete Status and fall within one year from the Date.

For problem 1,  I am using Count(DISTINCT {<ID = {"=Count({<Status = {'Running', 'Complete', 'Pending'}>} Status) > 1"}>} ID) but this also counts other ID rows with other Status values.

For problem 2, I am using Count(DISTINCT {<ID = {"=Count({<Status = {'Completed'},MonthYear ={">=$(=MonthName(addmonths(today(),-12)))"}>} Status) > 1"}>} ID) but this does not work either. The Set expression starts treating everything after 'MonthYear' as some kind of variable and gives errors.

I am attaching my QVW with some sample data. I have reached out before but couldn't really find the end solution for these 2 requirements. So reposting for bumping this question up. Any kind of help is highly appreciated.

I'm using AutoNumber(RowNo(),[MyField]) and it takes 25 minutes with 800k rows and I'm only going to have more as time goes by. (In SQL I'd use Row_Number() over Partition By, but I can't use that here.) How can I accomplish the same thing in substantially less time? 

Below is a Load Script that does what I want with some fake data. The point is to have a column that numbers the rows in which a patient visit took place in multiple departments on the same day. For example, a patient is admitted to the Emergency Department (ED) and transferred to the ICU. The ED row is '1', the ICU row is '2'. How can I do this for lots of data very quickly?

TempTable2:
LOAD * INLINE
[VisitNum, Dept, Date,
1000, ED, 2021-06-10
1001, ED, 2021-06-10
1001, ICU, 2021-06-10
1002, MedSurg, 2021-06-11
1003, ICU, 2021-06-10
1003, MedSurg, 2021-06-11
1004, ED, 2021-06-11
1004, MedSurg, 2021-06-11
];

TempTable1:
LOAD
*,
(VisitNum & '-' & Date) as PatientDayPresent
Resident TempTable2
;

DayPresent:
Load DISTINCT
*
,AutoNumber(RowNo(),PatientDayPresent) as PatientDayPresentIndex
Resident TempTable1;

Drop Tables TempTable2, TempTable1;

Hi All,

Consider below table,

Input:

A, B

1,Area

1,Club

2,District

3,Country

3,Street

Output:

While looking for Values 1 - Area, Club 2 - District, 3 - Country, Street as like i need multiple values as output.

Anybody can help. Thanks in Advance.

Hello,

I currently maintain a mapping excel sheet (lets call it Table A) that I upload to Qlik. The file contains Dimension values that are then associated to a different table (Table B) that I am uploading through a QVD. 

The dimensions in Table A have an almost hierarchical structure of "Object" --> "Program" --> "Project", where a single Program can be aligned to multiple Objects and there can be multiple Projects aligned under a single Program. 

I have a requirement to show and drilldown from the Object level to the Programs and then Projects that are found in Table A, which is straightforward. But, I also have to show all other Projects not found in Table A that are associated with the Program/Object being filtered on (think of a long filter pane with all these fields next to each other drilling  down). The issue is that Table A is a much smaller subset of Table B and the Object Name field is only found in Table A.

I am associating the two tables through a primary key, but since Table A is only a subset of data, as soon as an Object Name field is selected the Project Field only shows values contained in Table A and there is no way to see the other Projects that are in Table B. The rest of the projects can be found within the tables if you were to only use fields native to Table B, but I need to use the Object Name field coming from Table A as a filter. 

So far I have tried ApplyMap() without success since it is a one to many relationship, as well as a Link table that I cannot get to work. 

Any help would be much appreciated. Thanks in advance!