How to show the calculations in the simple Table

[Sara_3]

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' 

                or

                 [PO Released 1/0] = 1 

                )

            )

            or

            ([PR Deleted 1/0] = 1 

            and

                ("PO LI RelDT Flag" = 'N'

                or

                 [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.

[rubenmarin]

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-analysi... I have added an answer on how to make it dynamic.

[Sara_3]

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 

0 

0 

0

0

0

3

Jan 2023

1

1

1

0

3

9

Feb 2023

1

5

3

0

9

5

Mar 2023

1

5

4

0

10

9

[rubenmarin]

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

[Sara_3]

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.