8 Replies Latest reply: Aug 13, 2017 9:41 PM by Chan HS RSS

    getting data from the latest month

    Chan HS

      Hi,

       

      I have a table which contains data from excel spreadsheets from the start of the year until now and another table which reads the latest month's spreadsheet and places it in a separate table. To improve efficiency, i'm planning to remove the second table and use set analysis to grab the latest month data from the first table. As the script will constantly be pulling in new data every month, i need the result to be dynamic.

       

      This is the current expression that i am trying to do:

       

      Count({<Month = {'$(=Max(Month))'}>}Status)

       

       

      I have a field called status in every spreadsheet and i need to collect the total amount of status, hence i'm using the count function.

       

      Currently all that i'm seeing is a -.

      What am i doing wrong?

       

       

      Also, in the future i might need to separate the status between 'Open' and 'Close', therefore can i use the same expression to separate them?

       

      Thanks!

        • Re: getting data from the latest month
          Sunny Talwar

          Check what do you see when you use this in a KPI box object?

          =Max(Month)

           

          Is this giving you anything? or showing a number? If it is not showing anything (or '-'), then it might be the issue that Qlik Sense doesn't understand your Month field as a dual month field with underlying numerical value.

           

          You can try this

           

          LOAD Date,

              Month(Date) as Month,

              Num(Month(Date)) as MonthNum,

              ....

          FROM ....;

           

          and then this

          Count({<MonthNum= {'$(=Max(MonthNum))'}>}Status)

            • Re: getting data from the latest month
              Chan HS

              Hi,

               

              As it is now the weekend i am unable to confirm if you are correct.

               

              However, i believe that if i use the expression

              =Max(Month)

              it shows me the latest file name eg. Jun-17 as i set the Month field to be the names of the file in ascending order.

               

              As for the rest of the commands, i will try it out when i get back to work on Monday.

               

              Alright I have tried out the script and expression you have given and it works! Combined with bruno bertel's answer i have gotten what i needed.

               

              Thanks for starting me off!

            • Re: getting data from the latest month
              Andrea Gigliotti

              try this expression:

              sum( aggr( sum( if( Month = max(Month), Status, 0 ) ), Month ) )

               

              to separate the status between 'Open' and 'Close':

              sum( {< Status = {"Open"} >} aggr( sum( {< Status = {"Open"} >} if( Month = max(Month), Status, 0 ) ), Month ) )

              and

              sum( {< Status = {"Close"} >} aggr( sum( {< Status = {"Close"} if( Month = max(Month), Status, 0 ) ), Month ) )

                • Re: getting data from the latest month
                  Chan HS

                  Hi,

                   

                  I'm new to Qliksense (2 weeks in) with most of my knowledge from reading forums and online help, so i am unable to understand the expression that you have here. The aggr expression has confused me even after reading up on it so can you please give me a short explanation on what the expression does?

                   

                  As i said in my reply above, i am unable to confirm if your expression works until Monday.

                   

                  Alright I have tried out the expression but there is no result. If i place the expression in a KPI box object the number comes out to 0.

                   

                  Thanks!

                • Re: getting data from the latest month
                  bruno bertels

                  Hi Chan

                   

                  Sunny is right chek first this expression in a text box and see what happens : "-" nothing or a number or a monthname

                   

                  In your mesure what is month ? is it a field ? if yes what kind of data does that filed contained ? num ? integer ? Does Qlik recognize it as a date field ?

                   

                  try the mesure given by Sunny ( All his mesure helps , he is surely one of the best helper here )

                   

                  For second question of course you can use set analysis for status like that :

                   

                  Count({<Status= {'Open'}>}Status) will count the status that are OPEN

                  Count({<Status - = {'Open'}>}Status) see the minus sign before equal sign then it will count all the status that are different from Open

                   

                  You can also put several set in your mesure like that :

                   

                  Count({<Month = {'$(=Max(Month))'} , Status= {'Open'}>}Status) will count the status Open for the Max Month

                   

                  See this link to help you built your set analysis :

                   

                  http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

                   

                   

                   

                    • Re: getting data from the latest month
                      Chan HS

                      Hi,

                       

                      I don't think Qlik recognises it as a date field as my spreadsheet lists it as text. Using Sunny's method by converting it into a number, it now works.

                      I have also tried out your expressions that relate to my second question and it works too so it will help me out in the future.

                       

                      Thanks!

                    • Re: getting data from the latest month
                      omar bensalem

                      the max function works with numeric values; if your month field is a text field (jan, feb..), that won't work; you'll need to create a monthNum field:

                      Num(Month(Date)) as  monthNum,


                      and then use it within ur expressions:


                      Count({<monthNum = {'$(=Max(monthNum))'} , Status= {'Open'}>}Status)


                      Count({<monthNum = {'$(=Max(monthNum))'} , Status= {'Closed'}>}Status)