Solved: Re: Date Issue in Qlikview - Qlik Community

Anonymous · ‎2017-10-25

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

Anonymous · ‎2017-10-27

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)

View solution in original post

sergio0592 · ‎2017-10-25

Hi,

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

sunny_talwar · ‎2017-10-25

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

Anonymous · ‎2017-10-25

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.

sergio0592 · ‎2017-10-25

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')

Anonymous · ‎2017-10-25

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)

sunny_talwar · ‎2017-10-25

Not sure I understand, but may be you need this

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

or this

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

Anonymous · ‎2017-10-25

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.

sunny_talwar · ‎2017-10-26

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?

Anonymous · ‎2017-10-26

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.