topic Sum with variable dates in QlikView

Sum with variable dates

Thu, 03 Nov 2011 01:18:50 GMT

Hi All

I am unable to convert other answers to my file - I am new to QV, so am hoping someone can help:

I am trying to create a straight table showing:

Date1

Date2

Difference

I created variables for Date1 and Date2 to create a calendar for both, hoping to sum the number of complaints per area within a certain week: [Complaint_Weekend].

Is someone able to help with the Syntax for this, it would be much appreciated! So it should look something like

Sum of number of Complaints with [Complaints Weekend] = vDate1

Sum of number of Complaints with [Complaints Weekend] = vDate2

then the difference between the two.

Thanks in advance

Neil Allen

Sum with variable dates

Thu, 03 Nov 2011 01:50:25 GMT

Sum with variable dates

Thu, 03 Nov 2011 04:05:33 GMT

If you a field for number of complaints the following would work:

Sum({<[Complaints Weekend] = {"$(vDate1)"}>} numberofComplaints). Like wise for vDate2.

Hope this helps,

Kiran.

Sum with variable dates

Thu, 03 Nov 2011 04:14:01 GMT

Hi Kiran

The complaints are given a value of one, and use [Complaints_Counter], however this brought through 0's, with the variables, do i need to give this a format in the variable settings so it matches the complaints_weekend field?

Thanks for the reply

Sum with variable dates

Thu, 03 Nov 2011 04:17:00 GMT

Yes. Your variables output and [Complaints Weekend]'s format should match.

Kiran.

Sum with variable dates

Thu, 03 Nov 2011 04:20:50 GMT

Are you able to tell me how it should look in the variable settings, please?

Thanks

Sum with variable dates

Thu, 03 Nov 2011 04:24:28 GMT

If you make a list box on [Complaints Weekend], what format are you seeing? The format is not on variable settings but should be acheived with date#

Kiran.

Sum with variable dates

Thu, 03 Nov 2011 04:28:39 GMT

I was hoping to avoid having a list box and a calendar box - when i look in the variable settings after putting in a value in to the calendar it appears in number format, even though I have formatted the calendar as D/MM/YYYY.

Neil

Sum with variable dates

Thu, 03 Nov 2011 04:33:50 GMT

Neil,

I wasnt asking to have a list box. For set analysis to work the format should match.

if your Variable returns number add date function around it. Like date($(vDate1)).

Kiran.

Sum with variable dates

Thu, 03 Nov 2011 04:43:03 GMT

Sorry Kiran, Excuse my ignorance, do you mean to put the date($(vDate1)) in the variable settings, or the expression in the straight chart or someone else..

Again, sorry for my ignorance

Neil

Sum with variable dates

Thu, 03 Nov 2011 04:51:20 GMT

There is nothing to be sorry for. QV is easy to learn just give it some time

The format is to be added to the expression. For instance my original post

Sum({<[Complaints Weekend] = {"$(vDate1)"}>} numberofComplaints)

should be changd to

Sum({<[Complaints Weekend] = {"$(=date(vDate1))"}>} numberofComplaints)

If the problem persists can you share your qvw for us to debug?

Kiran.

Sum with variable dates

Thu, 03 Nov 2011 04:55:20 GMT

Thanks so much for you help Kiran!!!!!

Neil

Re: Sum with variable dates

Mon, 19 Nov 2012 16:57:11 GMT

Excellent post. Very helpful.