topic Re: Date Issue in Qlikview in QlikView

Date Issue in Qlikview

Anonymous — Wed, 25 Oct 2017 21:38:44 GMT

Hi Team,

I have come across a requirement in which the the product X has a lag of 2 days.

So when i filter the report for Week = 9/9/2017, then the Product X must show values for the week 9/11/2017.

Filter applied	Week:9/9/2017
Expected Output
X Week	Week	QTY
9/11/2017	9/9/2017	100
9/11/2017	9/9/2017	200
9/11/2017	9/9/2017	300
original Output
X Week	Week	QTY
9/9/2017	9/9/2017	80
9/9/2017	9/9/2017	90
9/9/2017	9/9/2017	110

Any help with this would be appreciated.

Thanks,

Harshala

Re: Date Issue in Qlikview

sergio0592 — Wed, 25 Oct 2017 22:06:36 GMT

Hi,

As a dimension X Week, add calculated dim : =AddMonths(Week,2)

Re: Date Issue in Qlikview

sunny_talwar — Wed, 25 Oct 2017 22:11:17 GMT

I think you might have missed the 2 days part... Shouldn't it be Week + 2 for 2 days?

Re: Date Issue in Qlikview

Anonymous — Wed, 25 Oct 2017 22:15:40 GMT

Hi,

This is a weekly report. All the fields are calculated at week level.

There are four different products, all the products must be filtered for the selected week date except for the product X.

so when i select week = 9/9/2017 (MM/DD/YYYY)). The Product X should be filtered for the week 9/11/2017 (MM/DD/YYYY) - ie: Week starting on Monday rather than saturday.

Re: Date Issue in Qlikview

sergio0592 — Wed, 25 Oct 2017 22:19:08 GMT

Yes, i confused date format DD/MM/YYYY and MM/DD/YYYY because i use to handle with DD/MM/YYYY, and it's late here

=date(Week+2,'MM/DD/YYYY')

Re: Date Issue in Qlikview

Anonymous — Wed, 25 Oct 2017 22:21:15 GMT

Hi Sunny,

Yes, it should be to add 2 days.

I have worked on an expression like date(Num(Weekdate)+2,'MM/DD/YYY') - this works fine as an calculated dimension. But fails in set analysis :

Sum({< weekdate = {"=$(=date(Num(Weekdate)+2,'MM/DD/YYY'))"}>}QTY)

Re: Date Issue in Qlikview

sunny_talwar — Wed, 25 Oct 2017 23:48:00 GMT

Not sure I understand, but may be you need this

Above(Sum({<weekdate>} QTY), 2)

or this

Above(TOTAL Sum({<weekdate>} QTY), 2)

Re: Date Issue in Qlikview

Anonymous — Thu, 26 Oct 2017 03:44:40 GMT

Let me put it this way :

Week date values : 09/02/2017, 09/09/2017, 09/23/2017, 09/30/2017

All these week are calculated starting from Saturday in a month.

All products are filtered in these week dates.

Except for X, as X is calculated on weekdays starting from Monday. in this case i.e: 09/11/2017.

So i need to write a condition in my set analysis so as to get the qty for product X in such a way that if the user selects 09/09/2017, the product X is filtered for 09/11/2017 and show the QTY for week date 09/11/2017.

Re: Date Issue in Qlikview

sunny_talwar — Thu, 26 Oct 2017 08:43:07 GMT

I am still not 100% sure I understand... would you be able to share a sample to show the kind of data you have and explain the output you are looking to get from it?

Re: Date Issue in Qlikview

Anonymous — Thu, 26 Oct 2017 22:34:47 GMT

In the sample file:

Date selected: 4/17/2014

sum(Value) = 100

Expected result;

Next day value: sum of value for 4/18/2014 should be displayed.

Every time i make a date selection , the value should be filtered for the next date.

Re: Date Issue in Qlikview

sunny_talwar — Fri, 27 Oct 2017 00:45:17 GMT

Try this

=Sum({1<date={"$(=date(num(date)+1))"}>}Value)

or this

=Below(TOTAL Sum({1<date={"$(=date(num(date)+1))"}>}Value))

Re: Date Issue in Qlikview

Anonymous — Fri, 27 Oct 2017 18:17:18 GMT

Hi Sunny, i tried the below expressions earlier it doesn't work.

Sum({1<date={"$(=date(num(date)+1))"}>}Value)

also the other expression :

=Below(TOTAL Sum({1<date={"$(=date(num(date)+1))"}>}Value)) - doesn't work if i remove the date field.

Solution: i need ignore the date selection

Sum({< weekdate = {"$(=date(Num(Weekdate)+2,'MM/DD/YYY'))"} ,date= >}QTY)