14 Replies Latest reply: Sep 17, 2015 5:13 AM by Pablo Luna RSS

    How to show two lines based on two fields based on two different types of dates

    Henco van Ee

      Hi all,

       

      I've got a challenge here

      I have two different types of dates. The first one is transactiondate (or invoicedate) and the second one is postdate (like pubdate).

      In QlikView I have a line chart which shows me the sum of sales. These sales are based on transactiondate and is a sum of transamount.

      X axis = TransMonth

      Y axis = sum of sales

       

      What I would like to do is to show the sum of sales forecast. This forecast can only be based on the postdate of course.

      The problem is that I use the TransMonth on the X axis and I think I need to keep it that way, because of my sum of sales which is invoiced.

       

      I would like to see two lines in my chart.

      1. sum of sales based on the fields transactiondate and transamount

      2. sum of sales based on the fields postdate and amount

       

      On the X axis I would like to see 12 months.

      On the Y axis I would like to see the sum of sales.

       

      Is there anyone who have some great ideas on this?
      Thanks in advance.

       

      Henco

        • Re: How to show two lines based on two fields based on two different types of dates
          John Witherspoon

          If you don't have enough data to cause a performance problem a date island is a common solution.  Create a third set of dates, then sum(if()) to match up your real dates:

           

          dimension    = islanddate
          expression 1 = sum(if(transactiondate=islanddate,transamount))
          expression 2 = sum(if(postdate=islanddate,amount))

           

          If that does have performance problems, a more complicated approach that should render much faster is to create a linkage table linking a master date to records that have either a matching transaction date or post date.

           

          LinkageTable:
          LOAD
          transactiondate as date
          ,'transaction' as datetype
          ,recordID
          RESIDENT YourTable
          ;
          CONCATENATE (LinkageTable)
          LOAD
          postdate as date
          ,'post' as datetype
          ,recordID
          RESIDENT YourTable
          ;

           

          And then:

           

          dimension    = date
          expression 1 = sum({<datetype={'transaction'}>} transamount)
          expression 2 = sum({<datetype={'post'}>} amount)

           

          Hopefully I got that right.  You probably won't need the set analysis part if you don't have transamounts and amounts on the same records.

            • Re: How to show two lines based on two fields based on two different types of dates
              Henco van Ee

              Hi John,

               

              Thanks for your reply, I will try your suggestions.

               

              Regards,

              Henco

              • Re: How to show two lines based on two fields based on two different types of dates
                Henco van Ee

                Hi John,

                 

                I have tried the first solution and it workst. But, as you mentioned, the performance is a problem right now.

                So, after that I tried to make a Link table with a mastercalendar, but unfortunately I cant get it done.

                 

                I have two tables from where the dates are:

                 

                TRANSACTION

                %INSERTION_ID

                Transdate

                Other transaction fields


                PUBLICATION

                %INSERTION_ID

                Pubdate

                Other publication fields

                 

                Could you please explain me more about making a link table?

                Thanks in advance.

                 

                Kind regards,

                Henco

                  • Re: How to show two lines based on two fields based on two different types of dates
                    John Witherspoon

                    I've attached a working example.  It has a bit more than you need, as it has three data tables, and two tables share an ID while the third uses a different ID.  But if you ignore OtherTable, I think it's basically your situation.  Records are linked to a single master calendar using a linkage table.

                      • Re: How to show two lines based on two fields based on two different types of dates
                        Glenn Renwick

                        Hi John,

                         

                        Good solution. I was trying to put a simple button on your app to do a basic data macro action based on your master date field.

                         

                        Something like:

                         

                        Action: Select in field

                        Expression: ='<=' & date(today()-1) & '>=' & date(today() - 28)

                         

                        But it just doesn't populate my selections - Any ideas why?

                        • Re: How to show two lines based on two fields based on two different types of dates

                          Hi John,

                           

                          I have seen your example qvw and I am a little bit confused. I have attached a modified version of your qvw application in which there is no need to create linkage table still we can achieve the desired result.

                          Please look into it and explain me whether it is correct or not?

                          After reading hencovanee post I thought that both of dates (transaction & publication) are present in the same table as two different fields. If they were then date island would be the perfect solution. But if it gives performance problem then what should be other solution?

                          I am asking this question because I am facing the same problem in one of my assignment where there are multiple dates are present in a table as different fields and I need to link them with master calendar table.

                          Thanks in advance,

                          Anosh Nathaniel

                            • Re: How to show two lines based on two fields based on two different types of dates
                              John Witherspoon

                              The more complicated data model still supports everything we were able to do before we added the combined calendar, because it still has all that data.  All of that information has been preserved.  The simpler data model throws away a lot of possibly valuable information in order to link the dates together.  If we don't need the original information, then throwing it away and using the simpler model is almost certainly a better approach.  But I assumed we'd want to keep track of the fact that ID 1 in the first two tables was the same thing, and that a created date means something different than a departure date.

                               

                              To make it more clear that ID has more than one date associated with it, I've added an ArrivalDate to Table2.  To make it more clear that Table1 and Table2 should remain separate tables, I've changed Table1 to an activity table, with two activities on ID 1.  Then I created a third example chart that DOESN'T use our new linkage table and combined calendar to show that we still support the original information and the kind of charts we might want to build with it.

                               

                              But I should be clear that there's no one RIGHT data model for all purposes.  How to model your data is entirely dependent on what your data means and how you want to interact with it.  All I'm giving here is an example, one way to link dates to a common calendar while preserving all of the original information intact (and in this case, with no change to its structure either).

                                • Re: How to show two lines based on two fields based on two different types of dates
                                  John Witherspoon

                                  Oops, I introduced a bug.  Since Table1 now has multiple rows per ID, we need another field to have a complete key.  I've added a sequence field since we might have multiple activities on a single date.  Since the linkage table has to specify the full key, it now also specifies the sequence.  This creates a synthetic key.  That should be fine in this case, because I do indeed want these tables connected by two fields.

                                    • Re: How to show two lines based on two fields based on two different types of dates
                                      Anosh Nathaniel

                                      Hi John,

                                       

                                      I am facing a problem to resolve a loop. I have created two qvw to explain my problem. I have following entities in my database:

                                      Hospital

                                      Brand: Which are known as drugs.

                                      TA: Therapeutic Area, One therapeutic Area can have multiple drugs but one drug have only one TA

                                      Access: Users have access to limited hospitals

                                      Direct Sales: Sales of a Drug (Brand) through a hospital

                                      MAT: Moving anual total for a drug and a hospital

                                      Donation: A hospital is given some amount of donation for a TA

                                       

                                      Now in first qvw, a synthetic table is being created which i have removed in second qvw by manually creating link table between brand and hospital.

                                      The only problem which I was not able to solve is the loop which is getting created between hospital, TA, Donation and Brand as I have to analyze Donation amount by Hospital and TA. Currently the chart it is giving correct result but I don't know when loosly coupled table will start giving wrong results.

                                       

                                      Hope I am able to explain my problem.

                                       

                                      Anosh Nathaniel

                                        • Re: How to show two lines based on two fields based on two different types of dates
                                          John Witherspoon

                                          Well, first, synthetic keys are not always bad, and I sometimes have one or two in my data models.  Replacing one with your own concatenated key is, in my opinion, usually a waste of both your and the computer's time.  Yes, I'm aware that people are taught otherwise in QlikView classes, and that the reference manual specifically suggests replacing them.  For my opinion and subsequent discussion, see this thread.  Note that I'm linking you to a post in the middle of the thread.  If you want the whole discussion, including experts that disagree with me, I'd read that post, then go back to page 1 and start over from the top.

                                           

                                          As for loops or loosely-coupled data, though, I never allow them in my data models.  I can't really say what the right model for your data would be.  I'd need a better understanding of your data, and time to devote that I just don't have.  One possibility would be a central fact table concatenating, say, the four tables with the HospitalId in them and adding some sort of type field to distinguish between the types of data.  Something else may be more appropriate, though.

                                            • Re: How to show two lines based on two fields based on two different types of dates

                                              I have four dates, two are in one table on rest are on individual tables. I want to join all four date to a Master Calender. I tried your above example using link table, first it was complaining about the loop and second thing performance issue. I have been trying to combining two dates using below code, prformance issues.... thanks for your help....

                                               

                                              Link:

                                              LOAD

                                              Distinct ClaimID,

                                              date(FirstDateOfService, 'MM/DD/YYYY') as Date,

                                              'Encounter' as DateType

                                              RESIDENT Claim

                                              ;

                                              //CONCATENATE (Link)

                                              //LOAD

                                              //ClaimID,

                                              //FirstBillingDate as Date,

                                              //'Billing' as DateType

                                              //RESIDENT Claim

                                              //;

                                              CONCATENATE (Link)

                                              LOAD

                                              ServiceLocationExpenseID,

                                              ExpenseDate as Date,

                                              'Expense' as DateType

                                              RESIDENT ServiceLocationExpense

                                              ;

                                               

                                              Calendar:

                                              LOAD * ,

                                              date(monthstart(Date),'MMM YYYY') as Month

                                              ;

                                              • Re: How to show two lines based on two fields based on two different types of dates
                                                Anosh Nathaniel

                                                Hi John,

                                                 

                                                I consulted with my friend and found a solution in which I have created Hospital-Brand-TA link table and all other table are joined through this link table. Attached is the solution qvw.

                                                 

                                                But now the problem is if I don’t get HospBrandLink table from db I need to generate it manually so that I can create ‘Link’ table. Hospital brand relationship is present in multiple tables like Direct Sales, MAT etc… I need to take all these table into consideration and need to find all possible combination of hospital – brand ids present in db.

                                                Am I thinking in correct direction and Is this solution correct?

                                                 

                                                Thanks in advance,

                                                Anosh Nathaniel

                                      • Re: How to show two lines based on two fields based on two different types of dates
                                        Pablo Luna

                                        Hi John or anyone how can open qvw files,

                                         

                                        Could you please share the load script? I have Qlik Sense so I can't open qvw files.. (very annoying).

                                         

                                        Thank you,

                                        Pablo