topic Re: Set Expression for leading and trailing months in QlikView

Set Expression for leading and trailing months

maahivee — Tue, 23 Jun 2015 20:31:23 GMT

Hello Friends,

I have a tricky requirement: I have to show the sum(sales) for in 2 cases;

1st case is Leading 5 months: In this i have to show the sum(sales) for last year this date forward to 5 months,

That means say today's date is 6/23/2015, my expression should be for 6/23/2014 to 11/23/2014 which is 5 months from today but last year.

2nd case is Trailing 12 months: this is just the sum(sales) for last 12 months i.e 6/23/2015 to 6/23/2014.

I tried plenty different ways but no luck on anything, i have tried like creating variables and using them in expressions and all those kinds of things in set expressions. Also keeping in mind i am not an expert at writing the set expressions.

That would be really great if you guys can help me out on this one.

Re: Set Expression for leading and trailing months

sunny_talwar — Tue, 23 Jun 2015 20:49:25 GMT

Try these:

1) {<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'M/DD/YYYY'))"}>}

2) {<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(Today(), 'M/DD/YYYY'))"}>}

These should help.

Best,

Sunny

Re: Set Expression for leading and trailing months

maahivee — Wed, 24 Jun 2015 13:47:16 GMT

Hello Sunindia, Thanks a ton for the response, But if i am using those expressions it is just giving zeros all over, can you please help me.

Re: Set Expression for leading and trailing months

sunny_talwar — Wed, 24 Jun 2015 13:49:34 GMT

Lets start from here:

What do you get when you paste ='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'M/DD/YYYY') in a text box? are the dates in the same format as your date field???

Re: Set Expression for leading and trailing months

maahivee — Wed, 24 Jun 2015 13:54:51 GMT

Thanks for the quick reply.. The above exp if i put it in a text box is giving a value..

>=6/24/2014<=11/24/2014

Re: Set Expression for leading and trailing months

sunny_talwar — Wed, 24 Jun 2015 13:56:35 GMT

Is this the same format you have Date in? (The left hand side of the equation below)

{<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'M/DD/YYYY'))"}>}

Re: Set Expression for leading and trailing months

maahivee — Wed, 24 Jun 2015 13:57:44 GMT

Yes, the date is in the same format, MM/DD/YYYY.

Re: Set Expression for leading and trailing months

sunny_talwar — Wed, 24 Jun 2015 13:59:39 GMT

Can you show your complete expression and some screenshots of data where you expect this expression to sum/count/min/max the data???

Re: Set Expression for leading and trailing months

maahivee — Wed, 24 Jun 2015 14:03:58 GMT

I am trying to so like a sum with the expression you gave me like this

Sum({<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'M/DD/YYYY'))"}>}Sales)

But this is the result i am getting out of both the expressions.

Re: Set Expression for leading and trailing months

sunny_talwar — Wed, 24 Jun 2015 14:06:49 GMT

Not sure why it isn't working, but I guess see if this makes any difference:

Sum({<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'MM/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'MM/DD/YYYY'))"}>}Sales)

If this doesn't work, would you be able to share a sample?

Re: Set Expression for leading and trailing months

Anonymous — Wed, 24 Jun 2015 14:08:29 GMT

Hi Can you use trellis chart on date function

Re: Set Expression for leading and trailing months

maahivee — Wed, 24 Jun 2015 14:09:56 GMT

sunindia- That is the same thing i am trying but it is not working.. . what do you exactly mean by sample??

Re: Set Expression for leading and trailing months

sunny_talwar — Wed, 24 Jun 2015 14:11:32 GMT

May be like 5-10 lines of data from your database relevant to the chart that you are trying to make. Problem is if I mock up some data it might work in there and still won't work for you. So the it would be best if we use your data to start with.

Re: Set Expression for leading and trailing months

maahivee — Wed, 24 Jun 2015 14:25:58 GMT

i have dumped some data to excel but how can i attach it here??

Re: Set Expression for leading and trailing months

sunny_talwar — Wed, 24 Jun 2015 14:28:16 GMT

See the two screenshots below:

Re: Set Expression for leading and trailing months

maahivee — Wed, 24 Jun 2015 14:34:52 GMT

Sunindia- I have attached the file here, Also while doing that i have found that the original date is not in the mm/dd/yyyy format instead in the number format. i thought it is in the date format since i forgot that i changed the format of it.

Re: Set Expression for leading and trailing months

sunny_talwar — Wed, 24 Jun 2015 14:44:51 GMT

Change your date in the script like this:

Table:

LOAD [Item Number],

Date(Date) as Date,

Sales

FROM

Community_169701.xlsx

(ooxml, embedded labels, table is TB02_20150624_101755);

and then try the expressions I gave your.

I am also attaching a sample qvw for reference,

HTH

Best,

Sunny

Re: Set Expression for leading and trailing months

maahivee — Wed, 24 Jun 2015 15:10:53 GMT

Sunny- I have tried it in both of these ways..

date(date) as date and also in

date(date,'mm/dd/yyyy') as date

but unfortunately both dint work in my case.

Re: Set Expression for leading and trailing months

sunny_talwar — Wed, 24 Jun 2015 15:15:11 GMT

Have you made an selection when you are seeing the output? Clear all selections so that we can check if it is because of a selection or is the expression not working at all

Re: Set Expression for leading and trailing months

maahivee — Wed, 24 Jun 2015 15:23:25 GMT

Yes- i definitely cleared all the selections.