topic Re: How to show the calculations in the simple Table in App Development

How to show the calculations in the simple Table

Sara_3 — Sat, 10 May 2025 04:03:46 GMT

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:

main:

LOAD *

,if([PR Released 1/0] = 1

and(

("Del On Flag" = 0

and

("PO LI RelDT Flag" = 'N'

[PO Released 1/0] = 1

)

([PR Deleted 1/0] = 1

and

("PO LI RelDT Flag" = 'N'

[PO Released 1/0] = 1

)

,1,0) AS [Count PR as Open 1/0]

;

LOAD *

,if([P Release Date]<=[MonthStart]

and

not isnull([P Release Date])

,1,0) AS [PR Released 1/0]

,if([Deleted On]>=[MonthStart]

and

not isnull([Deleted On])

,1,0) AS [PR Deleted 1/0]

,if([PO Release Date]>=[MonthStart]

and

not isnull([PO Release Date])

,1,0) AS [PO Released 1/0]

;

Load

1 as rowCount,

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];

//Sample data

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.

Re: How to show the calculations in the simple Table

rubenmarin — Sat, 10 May 2025 08:56:24 GMT

Hi, with that data the result of b and c is 0, there is only one row with [P Release Date]={"<=1/1/2023"},a and that row doesn't has any value on [PO Release Date] or [Deleted On].

In https://community.qlik.com/t5/App-Development/how-to-do-the-static-dates-into-dynamic-in-set-analysis-or-load/td-p/2517389 I have added an answer on how to make it dynamic.

Re: How to show the calculations in the simple Table

Sara_3 — Sat, 10 May 2025 15:23:37 GMT

Hi Rubenmarin,

Thanks , i really need help in this , i have tried multiple things to get data in the table.

when i run the script and see the values of 1b and 1c i got the value 1 for each .

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

we have data of month dec has 3 rows but 1 is eligible for the set expression

12/21/2022 0:00, -, 1/18/2023 ----------------------------------------this one is fulfil the above set analysis
12/23/2022 0:00, 2/27/2023, -
12/27/2022 0:00, -, -

--------------------------------------------------------------------------------------------------------------

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

12/21/2022 0:00, -, 1/18/2023
12/23/2022 0:00, 2/27/2023, - ----------------------------------------this one is fulfil the above set analysis
12/27/2022 0:00, -, -

---------------------------------------------------------------------------------------------------------------------

My expected result should be like this in table :(dec calculations i have not try in KPI but other months values are accurate)

Can we do these calculations in set analysis or do in the load editor?

I have created a table here but it gave me HTML error and changes values like below vertically.

MonthStart

Cal 1a

Cal 1b

Cal 1c

Cal 1d

sum 1a-1d

Cal 2

dec 2022

Jan 2023

Feb 2023

Mar 2023

Re: How to show the calculations in the simple Table

rubenmarin — Sun, 11 May 2025 08:25:15 GMT

Hi, you right, b and c is one, I had a date format issue.

About the question, if you have a date as dimension, and you want to apply each dimension value to the expression, then you can't use set anaysis with dynamic dates, as it is expanded and applied before the dimension valus are calculated, in that case you will need to use Aggr() and or 'If' to make comparisons, like:

- Count({<[Del On Flag]={N},[PO LI RelDT Flag]={N}>} If([P Release Date]<=YearMonth, [P Release Date]))

- Count({$<[Del On Flag]={N}>} If([P Release Date]<=YearMonth and [PO Release Date]>=AddMonths(YearMonth,1),[P Release Date]))

Re: How to show the calculations in the simple Table

Sara_3 — Sun, 11 May 2025 23:55:22 GMT

Hi,

Thanks for your response, i have tried an rangesum , Aggr() and or if statements too in set analysis but did not get the expected result. the above set expressions you mentioned gave me the 0 values. i am pretty sure we need to do this calculation i load editor where we comparison the dates data.

Do you know how to handle these calculations in load editor?

Sara.