topic Re: Expressions in combination with time interval and several dates in QlikView

Expressions in combination with time interval and several dates

pacoli2013 — Thu, 07 Sep 2017 13:32:06 GMT

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

Re: Expressions in combination with time interval and several dates

sunny_talwar — Thu, 07 Sep 2017 14:04:44 GMT

May be this

= if(Only(TOTAL {1<DateIntervalName = p(DateIntervalName)>} DateIntervalID) = 1, Sum(GLEntryAccountingCurrencyAmount),

Sum({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} GLEntryAccountingCurrencyAmount))

Re: Expressions in combination with time interval and several dates

pacoli2013 — Thu, 07 Sep 2017 14:57:11 GMT

Hello Sunny,

Super !!!

The expressions work great in the amount-column. In the Column RecieptPostingdate I only get dates in combination with the key when the date is equal the filter settings.

So when my setting is year = 2017, month = 07 and day = 07, I only see the date in the column Postingdate when is really 07-07-2017. On other Dates I see a '-'.

Is there a way that I always see the relevant data, or should I change my Key /Dimension (= removing the postingdate).

Regards Court

Re: Expressions in combination with time interval and several dates

sunny_talwar — Thu, 07 Sep 2017 15:30:24 GMT

Check now

Re: Expressions in combination with time interval and several dates

pacoli2013 — Fri, 08 Sep 2017 06:19:56 GMT

Hallo Sunny

what do you mean with Check now?

Re: Expressions in combination with time interval and several dates

sunny_talwar — Fri, 08 Sep 2017 09:10:18 GMT

If you open this link

Re: Expressions in combination with time interval and several dates

You will see an application attached.... that attachment is what I want you to check

Re: Expressions in combination with time interval and several dates

pacoli2013 — Fri, 08 Sep 2017 11:02:00 GMT

Thanks Sunny for your help,

Re: Expressions in combination with time interval and several dates

sunny_talwar — Sun, 10 Sep 2017 23:21:47 GMT

Please mark the response which actually helped you reach a solution instead of marking your own response as the correct response.

And, also if you got what you wanted, I would suggest you to close the thread by marking the correct response

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Expressions in combination with time interval and several dates

pacoli2013 — Tue, 12 Sep 2017 06:00:14 GMT

Hello Sunny, I marked your answer as helpful (the only mark I can set)

Best

Court

Re: Expressions in combination with time interval and several dates

sunny_talwar — Tue, 12 Sep 2017 08:36:02 GMT

I still see only your own response as marked as helpful