15 Replies Latest reply: Aug 17, 2015 10:37 AM by Danila Gromov RSS

    Count if (DateA-DateB<=XX)

    Danila Gromov

      Hello there!


      I have a problem with formulas in Qlik Sense

      Need to count all cases in which difference between two dates is less or equal to set period


      Trying this expression:

      Count({<([DateA]-[DateB])={"<=13"}>} [DateA])

      and have "Error in set modifier expression"


      What's wrong with it?



        • Re: Count if (DateA-DateB<=XX)
          Sunny Talwar

          Try this:


          Count({<DateA = {"=DateA - DateB <= 13"}>} [DateA])

          • Re: Count if (DateA-DateB<=XX)
            Stefan Wühl

            - Within the set expression set modifier, only field names are allowed left of an equal sign (the equal sign is not to be read as comparison, but as assignment operator).


            - What Sunny suggests is syntactically correct, but won't really work if a DateA value could have more than one relation to a DateB value (so the advanced search can result in an ambiguous answer). You would need to operate the set modifier on a key field for DateA - DateB relations to get a correct result.

            =Count({<DateCombinationKey = {"=DateA - DateB <= 13"}>} DateCombinationKey)

              • Re: Count if (DateA-DateB<=XX)
                Sunny Talwar

                That was very informative Stefan. I was never really sure how this worked, but I think from you response, I created a test sample and decided to understand the logic of what you explained above. And after testing it out, it makes perfect sense. I might still need to spend some more time on this to fully digest it, but I have a good base now.




              • Re: Count if (DateA-DateB<=XX)
                Danila Gromov

                Thanks everyone

                • Re: Count if (DateA-DateB<=XX)
                  Danila Gromov

                  Guys, I have questions again;)


                  I need to find difference between average of columns (dates for Event A and Event B) for 1mln strings

                  So i'm using: Avg([Date_of_event_B])-Avg([Date_of_event_A]) which works well


                  In real life event B always goes after event A

                  Problem is that sometimes dates in inform systems are wrong, so I receive negative value for [Date_of_event_B]-[Date_of_event_A]

                  So i need to count Avg([Date_of_event_B)-Avg([Date_of_event_A]) only for those strings where [Date_of_event_B]-[Date_of_event_A]>0 not taking rest of strings into account


                  I can count number of such strings with formula:

                  Count({<EventAToEventB={"=[EventB]-[EventA]>0"}>} EventAToEventB) where EventAToEventB is date combination key

                  But Avg({<EventAToEventB={"=[EventB]-[EventA]>0"}>} EventAToEventB) doesnt work, showing "-" values

                  What should be right formula?

                    • Re: Count if (DateA-DateB<=XX)
                      Stefan Wühl

                      What is the format of field EventAToEventB, a text format?


                      I think you would want something like

                      Avg({<EventAToEventB={"=[EventB]-[EventA]>0"}>} [EventB]-[EventA])


                      or create a field with the difference in your script, like




                           [EventB]-[EventA] as [B-A]

                      FROM ...;


                      =Avg({<[B-A] = {">=0"}>} [B-A])

                        • Re: Count if (DateA-DateB<=XX)
                          Danila Gromov

                          format of fields EventA, EventB is

                          01.06.2015 21:38

                          EventAToEventB is a field created in script:

                          EventB&'-'&EventA as EventAToEventB

                          I guess format is just number


                          I tried =Avg({<[B-A] = {">=0"}>} [B-A]) and it returns "-" values


                          but this thing worked out!

                          Avg({<EventAToEventB={"=[EventB]-[EventA]>0"}>} [EventB]-[EventA])

                          Thank you!

                            • Re: Count if (DateA-DateB<=XX)
                              Danila Gromov

                              Next step

                              If I need to limit possible values from both sides?


                              Count Avg([Date_of_event_B)-Avg([Date_of_event_A]) only for those strings where




                              not taking rest of strings into account

                              I tried

                              Avg({<EventAToEventB={"=[EventB]-[EventA]>0"}>} {<EventAToEventB={"=[EventB]-[EventA]<200"}>}[EventB]-[EventA])

                              which works wrong ( returns negative values, I guess it takes only 2nd limitation (<200))


                        • Re: Count if (DateA-DateB<=XX)
                          Danila Gromov

                          New question:)


                          One colomn has numbers of unique transactions formatted like:

                          xz123456789ys, where xz,ys - letters

                          I need to find out transactions which start with letters ZX and finish with letters SD



                          ZX789654222SD etc

                          If colomn name is ID what formula should be?

                            • Re: Count if (DateA-DateB<=XX)
                              Stefan Wühl

                              If( Left (ID,2) ='ZX' and Right(ID,2) ='SD', 1,0) as IDFlag


                              If ( ID Like 'ZX*SD', 1,0) as IDFlag


                              or as calculated dimension:

                              =If( Left (ID,2) ='ZX' and Right(ID,2) ='SD', ID)


                              or using set analysis:

                              Count({<ID = {"ZX*SD"}>} Distinct ID)

                                • Re: Count if (DateA-DateB<=XX)
                                  Danila Gromov


                                  And what if i need to separate all IDs into 3 groups:

                                  1. 'ZX*SD'+'ZX*FG'

                                  2. 'LK*SD'

                                  3. others

                                  I add to script:

                                  If ( ID Like 'ZX*SD' and Like 'ZX*FG', 1,0) as ZX

                                  If ( ID Like 'LK*SD', 1,0) as LK

                                  and how to group rest of IDs to make 3rd group?

                                  Something like:

                                  If ( ID NotLike 'ZX*FG' and NotLike 'ZX*SD' and NotLike 'LK*SD', 1,0) as Rest

                                  Another step is to add a filter to worksheet to switch between this groups

                                  Now I can add separate filters for ZX and LK and choose between 1 and 0 to count or not count such strings

                                  I'd like to have 1 filter that allows turning on/off all of 3 groups ( ZX,LK,Rest)

                                  I guess I need smth like setting (ZX,LK,Rest) as TypeOfOperation and adding filter TypeOfOperation

                                  Another question

                                  I need to count number of strings which have equal text in 2 different colomns

                                  This seems very easy, I try:

                                  Count({<Colomn1={"=Colomn2"}>} Colomn1)

                                  Count({<Colomn1={"=[Colomn2]"}>} Colomn1)

                                  Count({<Colomn1={"=[Colomn2]"}>} [Colomn1])

                                  and none of it work

                              • Re: Count if (DateA-DateB<=XX)
                                Danila Gromov

                                So, I managed to fix database to have 3 groups for 1st question


                                Still cannot find strings with equal colomns


                                I have one file showing Region and fact Sorting Center (SC) for real transactions

                                1.NY - SC1

                                2.NY - SC3

                                3.LA - SC2

                                4.MA - SC1

                                5.WY - SC1

                                6.MA - SC2

                                Another file has Regions and Target Sorting Centers:

                                NY - SC1

                                LA - SC1

                                MA - SC1

                                WY - SC3


                                I need to separate strings which were presorted to right (Target)  sorting center

                                In my example strings 1,4 were presorted right


                                I tried to put it in script, like that:


                                FactSC&'='&TargetSC as RightPresorting

                                FactSC&'<>&TargetSC as WrongPresorting

                                But FactSC and TargetSC are loaded from different sources and QlikSence shows mistake


                                Another way is to make formula for pivot table, I tried

                                Count({$<TARGETSC={"=[FactSC]}>} [TARGETSC]) which doesnt work


                                Any thoughts on right formula/ approach?

                                  • Re: Count if (DateA-DateB<=XX)
                                    Stefan Wühl

                                    You can MAP your targets to your facts and then perform the check:



                                    MAPPING LOAD

                                    Region, [Target Sorting Center]

                                    FROM 'AnotherFile';


                                    LOAD *,

                                          if([Sorting Center] <> Target, 'Wrong','Right') as Presorted;



                                         [Sorting Center],

                                         Applymap('MAP',Region,'no mapping') as Target

                                    FROM 'OneFile';

                                      • Re: Count if (DateA-DateB<=XX)
                                        Danila Gromov

                                        I tried to modify script several times and still no success

                                        In fact my task is a bit more complicated than I stated in 1st message

                                        1st file has 1 mln strings with factSC full name and destination index (the task is about logistics)

                                        2nd file has correspondence between factSC full name and factSC

                                        3rd file has correspondence between destination index and destination region

                                        4th file has correspondence between region and targetSC

                                        I need to find out strings which were prosorted right, factSC=targetSC

                                        What I try is:



                                        MAPPING LOAD

                                        factSC full name, index

                                        FROM 1st file;


                                        MAPPING LOAD

                                        factSC full name, factSC

                                        FROM 2nd file;


                                        MAPPING LOAD

                                        index, region

                                        FROM 3rd file;


                                        MAPPING LOAD

                                        Region, targetSC

                                        FROM 4th file;


                                        LOAD *,

                                              if([factSC] <> targetSC 'Wrong','Right') as Presorted;

                                            Applymap('MAP',Region,'no mapping') as Target


                                        I wonder if I can make 4 MAPPING LOADs

                                        What is Applymap for?

                                        What source I put after it ( it has data from different files (2nd and 4th)?