8 Replies Latest reply: Aug 1, 2018 9:22 AM by Shahbaz Khan Mohammed RSS

    Count if Function (Income Increase)

    Mohammed Al Radi

      Dear Qlik Community,

       

      I hope this finds you well. I am currently trying to develop an expression to measure '# of Workers Whose Monthly Income Increased by at Least 10%'


      This is the expression I have for Current Income:

       

      avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income]) + avg(aggr(sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], MonthYear))

       

      This is the expression I have for Pre Income:

       

      avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income])

       

      The Identifier is:

       

      [Identifier Code]

       

      Usually I would insert a '(Current Income - Pre Income)/Pre Income as Income Increase' expression in the script then simply do a

       

      Count ( {<Income Increase = {'> .1'}>}[Identifier Code])

       

      but in this current data model I have two separate tables. The first containing worker dimensions including their monthly pre-income (this is static and does not change). The second containing the income they earn via the new job (it is a leather workshop that they work part time in so the current income would be the pre monthly income + whatever they earn from this - hence the addition in the 'Current Income' expression above) where they get paid per batch delivered. So in one month they might have four rows of data detailing the different batches they delivered and how much they got paid for it.



      With this model I am struggling to create a Count(If statement work. Any suggestions or ideas would be much appreciated


      Thank you for your support.


      Best,


      Mohammed


        • Re: Count if Function (Income Increase)
          Shahbaz Khan Mohammed

          Can you provide a sample XL data file  or app?

            • Re: Count if Function (Income Increase)
              Mohammed Al Radi

              Dear Shahbaz,

               

              Please find attached the sample sheet and enclosed the script:

               

              [Dimension Sheet]:

              LOAD

                  "Identifier Code",

                  "Age",

                  "Education Status",

                  "Marital Status",

                  Boy,

                  Girl,

                  "# of  Sessions Attended",

                  "Training Attended",

                  "Monthly Income",

                  "Production Team Member"

              FROM [xxxxxxx [Dimension Sheet]);

               

               

              [Fact Production Leather Workers]:

              LOAD

                  "Identifier Code",

                  "Date Produced",

                  "Date Produced" as date,

              //     "Financial Year",

              "Product #" & '+' & "Batch #" & '+' & "Financial Year" as Key,

                  "Quantity Produced",

                  "Cost per Unit",

                  Remuneration,

                  'Date Produced' as Datetype,

                  "Payment Status"

               

              FROM [xxxxxx [Fact Production Leather Workers]);



              Temp:

              Load num#([date],'#') as NumericDate,

              Key

              RESIDENT [Fact Production Leather Workers];

               

              RESIDENT [Fact Order Sheet];

               

               

              MinMax:

              LOAD

              Min(NumericDate) AS MinDate,

              Max(NumericDate) AS MaxDate

              RESIDENT Temp;

               

               

              LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

              LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

               

               

              MasterCalendar1:

              LOAD Distinct

              Date($(vMinDate) + RecNo() - 1) AS [date],

              Year(Date($(vMinDate) + RecNo() - 1)) as [Year],

              Month(Date($(vMinDate) + RecNo() - 1)) as [Month],

              Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MM-YYYY') AS [MonthYear],

              'Q'&ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&'-'&Year(Date($(vMinDate) + RecNo() - 1)) As QuarterYear

               

               

              AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

              DROP TABLE MinMax;

              DROP TABLE Temp;

               

               

               

              I have been trying to construct a count if statement along the lines of:

               

              Count [Identifier Code]  if (

               

              avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income]) + avg(aggr(sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], MonthYear))


              -


              avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income])


              / avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income])


              > .1


              but no luck. Thank you for your support!