9 Replies Latest reply: Sep 12, 2017 4:36 AM by Sunny Talwar RSS

    Expressions in combination with time interval and several dates

    Court van de Lisdonk

      Hello,

       

      I have a Qlikview Document where I have loaded three tables: Invoices, Receipts and Orders in the script. Every tables has more than one date-field: documentdate,, creationdate, postingdate and loadingdate

      I have also defined an inline table DateIntervall with a flag and a field, this interval is needed to switch from data only for today to data till to day and back.

      So I have made a (cycle) timegroup with the most important dates: DocumentDate, PostingDate and Creationdate.

      Then I have created two sheets, the first sheet is with filters and two straight tables, the second sheet is with the calendar I have created.

      I have made a straight table where I want to calculate different amounts. The sum of columns will be extended, dimensions etc. will be included

      In the script I have defined ReceiptPostingDate as Date, so the timefilters of my calander are related with ReceiptPostingDate.

       

      I have made some expressions for the different amounts (from the three tables):

       

      = if(DateIntervalID = 1, Sum(GLEntryAccountingCurrencyAmount),  Sum({$ < Year = {$(= max(Year))}, Month = {"<= $(=max(Month))"}, Quarter = > } GLEntryAccountingCurrencyAmount)) 

       

      = if(DateIntervalID = 1, Sum(InvoiceLineAmountCompanyCurrency),  Sum({$ < Year = {$(= max(Year))}, Month = {"<= $(=max(Month))"}, Quarter = > } ReceiptLineValueCompCurrency)) 

       

      = if(DateIntervalID = 1, Sum(InvoiceLineAmountCompanyCurrency),  Sum({$ < Year = {$(= max(Year))}, Month = {"<= $(=max(Month))"}, Quarter = > } InvoiceLineAmountCompanyCurrency)) 

       

      The expressions, I have defined, don’t work as wanted.

       

      What do I want to achieve:

       

      When DateIntervalID = 0, I want to see all data, all postingdates, amounts etc. till the date I set in the time filters: examples:

      When I set yearfilter = 2017, I want to see all data till end 2017 (31-12-2017), also data from years before 2017.

      When I set yearfilter = 2017 and monthfilter = 09, I want to see all data till end September (09) 2017 (30-09-2017) also all data from years before 09-2017 like 01-05-2015 or 20-11-2016 etc.

      When I set yearfilter = 2017, monthfilter = 09 and dayfilter = 07, I want to see all data to 07 September 2017 (07-09-2017) also data from years, months and days before 07-09-2017 like 01-05-2015 or 20-11-2016. Date from 07-09-2017 also.

       

      When DateIntervalID = 1,  I want to see only data, all postingdates, amounts etc. from the date I set in the time filters: examples:

      When I set yearfilter = 2017, I want to see all data from 2017 so from 01-01-2017 till 31-12-2017, not data before or after 2017.

      When I set yearfilter = 2017 and monthfilter = 09, I want to see all data from month 09 in 2017 so 01-09 till 30-09-2017.

      When I set yearfilter = 2017, monthfilter = 09 and dayfilter = 07, I want to see only all data from 07 September 2017, no data from other dates.

       

      Quarter, week and day shortname will have no influence on the filtered data.

       

      I will create other calendars to do the same with InvoicePostingDate and GLEntryPostingDate,  the (cycle) timegroup will be deleted or extended with all other dates, that depends on the users.

       

      An other option I have in mind is to create a switch for ReceiptPostingDate, InvoicePostingDate and GLEntryPostingDate and build that into the time filters when that is possible with the expressions.

       

      I’m not a hero in Set Analysis, so I ask for help once more and I hope someone can help me with the expressions

      Thanks in advance


      Regards Court