topic Regarding date selection issue in QlikView

Regarding date selection issue

d_koti24 — Thu, 19 Nov 2015 10:22:40 GMT

Hi All,

I have table like this:

period	sales
01/07/2015	100
01/08/2015	80
01/09/2015	120

my requirement is :

if i select 01/08/2015 in period ,i want to show the sales difference between 08/2015 ,07/2015 in text object.

ex: in text obj i want show the diff :100-80 =20

if i select 1/09/2015, in text obj i want show the diff :120-80 =40

if i select 1/07/2015 ,i don't have previous month so i want to show 100.

How to achive this?

Regards,

Re: Regarding date selection issue

rubenmarin — Thu, 19 Nov 2015 10:36:40 GMT

Hi dk, can you try this?;

Sum({<period={'$(=Max(period))'}>} sales) - Sum({<period={'$(=AddMonths(Max(period), -1))'}>} sales)

I set the first part as Max(period) in case there aremore than one possible period value in the selections. Maybe you need to use Date() to assign the date format.

Re: Regarding date selection issue

Gysbert_Wassenaar — Thu, 19 Nov 2015 10:36:54 GMT

Try Sum(sales)-Sum({<period={"=$(date(monthstart(max(period),-1),'MM/DD/YYYY'))"}>}sales)

If that doesn't work make sure that period is a date field and not a text field. Use the date#() function in the script if necessary to turn text values into date.

Re: Regarding date selection issue

effinty2112 — Thu, 19 Nov 2015 11:11:42 GMT

In a straight table as this expression

sum(sales)-Above(sum(sales))

This will give null for the first row but if you really want the first row to show sum(sales for the difference use:

if(RowNo()=1, sum(sales), sum(sales)-Above(sum(sales)))

Cheers

Re: Regarding date selection issue

HirisH_V7 — Thu, 19 Nov 2015 11:33:51 GMT

Hi,

Variables:

vDate=Date(Max(Date),'DD/MM/YYYY')

vDate2=Date(MonthStart(Max(Date),-1),'DD/MM/YYYY')

In Text Box:

(Sum({<Date={"$(vDate)"}>}Sales) -Sum({<Date={"$(vDate2)"}>}Sales))

PFA

Hope this Helps,

Regards,

Hirish