Hi All,

I really need help !

I am pulling the data from the excel sheet but here i am creating the inline table as a sample data. my actual data is from 2019 till 2024. 

Expected data of Jan 2023:

Jan 2023 :  Cal 1a = 1

                 , Cal 1b = 1,

                   Cal 1c = 1,

                   Cal 1d = 0 ,

                  final cal sum of 1a- 1d = 3 ,

                   Cal 2 = 9 

Requirements : 

My requirement are consist of 2 calculations and the first 1 is consist of sum of 4 calculations by month. I have created the following set analysis of these calculations with static dates but dont know how to do dynamically in table.

Calculation 1: for example Jan 2023:

                                          1-a: Count({$<[P Release Date]={"<=1/1/2023"}, [Del On Flag]={N},[PO LI RelDT                                                         Flag]={N}>}[P Release Date])

                                          1- b: Count({$<[P Release Date]={"<=12/31/2022"}, [Del On Flag]={N},[PO Release                                                      Date]={">=1/2/2023"}>}[P Release Date])

                                          1-c: Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO LI                                                    RelDT Flag]={"N"}>}[P Release Date])

                                          1-d: Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO                                                       Release Date]={">=1/1/2023"}>}[P Release Date])

Calculation 2: for example Jan 2023:

Count({$<[P Release Date]= {">=1/1/2023<=1/31/2023"}>}[P Release Date])

===========================================================================

I have tried the following code in script for first calculation  but did not work as expected and dont know how to do second calculation:

load * inline [
P Release Date, Deleted On, PO Release Date
12/21/2022 0:00, -, 1/18/2023
12/23/2022 0:00, 2/27/2023, -
12/27/2022 0:00, -, -
1/4/2023 0:00, 1/24/2023, -
1/4/2023 0:00, -, 1/17/2023
1/4/2023 0:00, -, 2/28/2023
1/4/2023 0:00, -, 11/30/2023
1/6/2023 0:00, -, 2/9/2023
1/6/2023 0:00, -, 2/16/2024
1/11/2023 0:00, 5/10/2023, -
1/17/2023 0:00, 3/17/2023, -
1/17/2023 0:00, -, 4/11/2023
2/2/2023 0:00, -, 2/7/2023
2/2/2023 0:00, -, 6/21/2023
2/6/2023 0:00, 6/6/2023, -
2/15/2023 0:00, 9/11/2023, -
2/15/2023 0:00, -, 3/14/2024
3/2/2023 0:00, -, 5/3/2023
3/2/2023 0:00, -, 8/16/2023
3/2/2023 0:00, -, 9/22/2023
3/6/2023 0:00, -, 1/2/2024
3/30/2023 0:00, 4/11/2023, -
3/30/2023 0:00, 6/7/2023, -
3/30/2023 0:00, 8/7/2023, -
3/30/2023 0:00, -, 4/20/2023
3/30/2023 0:00, -, 8/29/2023
];

Thanks in advance,

Sara.

Hello,

i'm trying to setup a data lakehouse on AWS S3 using delta lake tables. I don't need the big data framework so i want to use a standard job, but i can't understand clearly how to set up a connection with delta lake component to use S3, if possible. I can't find any explanation in the documentation, which frankly is very poor about this topic and made me confused.

Does a standard job need Databricks or it still use spark? Is talend (java) the compute engine instead? I see i should use a jdbc driver and i wonder how do i configure the jdbc string for connection, can anyone give me any help or point me to a guide or additional documentation?

Thanks in advance,
Vincenzo.

Hi all,

The new capacity model of Qlik counts the data for analysis in GB as consumption. This consists of two elements in the management console: 

1. App reload

2. Datafile

Now we encounter some issues with our 'regular' way of working, where the same app is counted twice in capacity consumption. Once with the shared space app and once with the managed space app. Our view of a shared space was that the collaborative editing part was one of its strong points. Therefore, we had the 'source' app in a shared space and the 'published' app in a managed space. With the capacity consumption now counting twice, we are somewhat forced to move the app to a personal space, reload  the app with necessary changes, and then move the app back to the shared space. Reloads in a personal space are not counted in capacity consumption. Furthermore, an app that has been reloaded 2 months ago is still counted as capacity consumption in current month!! In my perspective, a monthly check on consumption should only check that particular month. This implies that even if the shared space app has not been reloaded for months, it still counts twice in capacity consumption. The only fix I found was deleting and re-uploading the app. I cannot imagine that this is the desired way of working! What are your views and best practices on working with this new capacity model?

Hello.
We use data replication using Qlik Attunity, data is taken from SQL Server 2019, everything was fine. Then, after updating SQL Server to version 2022, difficulties began. Multiple errors and locks began when the task was started after stopping, the task was not deleted or updated, the task was simply suspended and started.

The errors look like this

Stream component 'st_0_BPMSQL_BPM' terminated
Stream component failed at subtask 0, component st_0_BPMSQL_BPM 
Error executing source loop
Endpoint is disconnected
Failure in starting (with TXN consistency...)
Failure validating initial LSN 'anywhere existance'
mssql_lsn_anywhere_validator(...)Failed in looking at Active TLOG
lsn_exists(...)Failed in statement creation for looking at Active TLOG

Failure in approving table full logging setup for dbo.AccountInsuranceProgram

mssql_approve_table_full_logging_setup(...) failed in checking PK presence for table identified by ID '1882736243'

The blockages look like this and there are a lot of them (see screenshot)

Please help me solve the problem, thank you

Hello Experts,

I'm encountering an issue in QlikView involving a straight table. The problem arises from certain part numbers having multiple entries in the cut_in_yr column, specifically two different years. This is causing unintended duplication of other values in the table.
The duplication is particularly problematic when users export the data to Excel, as it's affecting the accuracy of totals. I'm seeking a solution to eliminate these duplicates and ensure the data integrity, especially for Excel exports.
Can you suggest an effective approach to resolve this duplication issue while maintaining the correct representation of data?

In the above screenshot, the blue colored column Cut in Year is having two years, which is needed, but other non-coloured columns values are getting duplicated. How can i avoid this? 

Hi Community,

I recently completed and passed the QSBA (Qlik Sense Business Analyst) certification exam. I was able to view my result and received a confirmation after passing. However, I did not pay any exam fee throughout the process.

Could someone please confirm if this exam was part of a promotional offer or if it’s typically a paid exam? I’m a bit confused as most certification exams usually require a payment.

Thanks in advance for your help!

Hi,

The following are set analysis i have used in the KPI but i need to present the data in simple table:

i have the actual data from 2019 - 2023 but the following are the set analysis of Month Jan 2023:

1- Count({$<[P Release Date]={"<=1/1/2023"}, [Del On Flag]={N},[PO LI RelDT Flag]={N}>}[P Release Date])

2- Count({$<[P Release Date]={"<=12/31/2022"}, [Del On Flag]={N},[PO Release Date]={">=1/2/2023"}>}[P Release Date])

3- Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO LI RelDT Flag]={"N"}>}[P Release Date])

4- Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO Release Date]={">=1/1/2023"}>}[P Release Date])

Main Data table:

Load
if(date(Date#("Deleted On", 'MM/DD/YYYY')) > 0 ,'Y','N') as "Del On Flag",
if(date(Date#("PO Release Date",'MM/DD/YYYY')) > 0 ,'Y','N') as "PO LI RelDT Flag",

Date(Date#([P Release Date], 'M/D/YYYY h:mm'), 'MM/DD/YYYY') as [P Release Date],
Date(Date#([Deleted On], 'MM/DD/YYYY')) as [Deleted On],
Date(Date#([PO Release Date], 'MM/DD/YYYY')) as [PO Release Date],
monthstart((Date#([PO Release Date], 'MM/DD/YYYY'))) as [MonthStart]

from excel sheet;

Thanks,

Sara.

Good afternoon, dear friends.

I'm having a problem applying the sum(TAX_REMAINING) measure in a simple table report. It shows the same value three times, when it should only display one value and the other two values ​​are zero. This is a native error from the database.

Here's an example of how my report is displayed:

So, in the tax code field it shows 3 details and of those it should only show 1 value as shown in the following image:

Therefore, the report already shows me that and I am applying the following measure:

If(not IsNull(aggr(SUBTOTAL_LINEA, NUMERO_TRANSACCION)),SUM(SUBTOTAL_LINEA),0)

But when I select another branch and the transaction number, in my subtotal field it shows me all 0, when it should show me the same different values ​​as they appear in the last column Subtotal MXN, so I don't know how to apply my measure.

Please, I need to know how to apply the formula so I can visualize the data.

I appreciate your support.

I look forward to your comments.

In the column "Total", I have a calculated measure, given by the following expression:

Count(DISTINCT {
<
origemEventoAtencao = {'1', '2', '3'},
tipoInternacao = {'1','2','3','4','5'},
regimeInternacao = {'1', '2'},
guiaSolicitacaoInternacao = {"*"},
dataRealizacao = {
">=$(=Date(AddMonths(Max(dataCalendario), -12)))<=$(=Date(Max(dataCalendario)))"
}
>
} guiaSolicitacaoInternacao)

+

Count(DISTINCT {
<
diariasUTI = {">0"},
guiaSolicitacaoInternacao = {"*"},
dataRealizacao = {
">=$(=Date(AddMonths(Max(dataCalendario), -12)))<=$(=Date(Max(dataCalendario)))"
}
>
} guiaSolicitacaoInternacao)

In the column "Contribuição individual", the expression is a simple ratio, being the expression above divided by the sum of all values of "Total".  

My problem is to calculate the cumulative percentage. From the example, the values of "Percentual acumulado" must be presented as shown in the figure below

I have already tried using the above, range sum and row functions, however, none of them generated the cumulative sum, and it also messed up the order of the rows.

If it helps, the rows are sorted like this: