topic Sum from mindate to selected date in QlikView

Sum from mindate to selected date

Tue, 31 Jan 2012 09:27:29 GMT

Hi,

I have a list of projects which all have different start dates. I would like to make a "project to date" sum that works with the calendar function, meaning that if the start period is OCT-10 and I select 2011-06 in the calendar, I would like to see the sum from OCT-10 to 2011-06.

I have made a date out of period field OCT-10 (or similar) to look like 20101001 to include the calendar.

Example:

Project_ID	Date	Invoice	Cost
1	2010-10-01	A	500
1	2011-03-01	B	200
2	2011-04-01	C	2500
1	2011-05-01	D	400
2	2011-05-01	D	500
2	2011-06-01	E	1000
1	2011-07-01	F	300
1	2011-08-01	G	600
2	2011-09-01	H	1500
1	2011-11-01	I	100

From above table,if I in the calendar select 2011-08-01, I would like the sum(Cost) for each project from it's min(Date) to selected date. The function needs to work in a pivot table and chart as well, I would love to be able to pivot the Project_ID field and expand it to show the detaild level. *Update* Forgot to add field Invoice as a detaild level that holds information of the cost.

Any help would be appreciated!

Robert

Re: Sum from mindate to selected date

swuehl — Tue, 31 Jan 2012 10:12:11 GMT

I think you could use a set expression like

=sum({<Date= {"<=$(=max(Date))"}>} Cost)

for this, in combination with a dimension Project_ID.

Sum from mindate to selected date

Tue, 31 Jan 2012 15:09:57 GMT

Hi,

that worked perfectly fine! Thak you very much!

I'm not very used to the set expressions yet. Thus, I'm adding yet another question to expand this model .

In a new column next to the sum column you showed above, I would like to see the sum untill month before. If I select 2011-08-01 in your attached application, I get the sum 6000, and if I select 2011-07-01 I get 5400. I would like 2 columns, one showing the 6000 and one showing 5400.

Robert

Sum from mindate to selected date

swuehl — Tue, 31 Jan 2012 15:12:48 GMT

Just add another expression:

sum({<Date= {"<=$(=addmonths(max(Date),-1))"}>} Cost)

Regards,

Stefan

Sum from mindate to selected date

Tue, 31 Jan 2012 15:23:34 GMT

Hi,

it works in the test application, but in my build, the date format is 20110801 (instead of 2011-08-01). Is there a way to format the date in the set expression?

The two expressions looks like this when selecting 20110901

=sum({<Date= {"<=20110901"}>} Cost)

=sum({<Date= {"<=2011-08-01"}>} Cost)

Robert

Sum from mindate to selected date

swuehl — Tue, 31 Jan 2012 15:26:01 GMT

Date() function should do the job. Try

sum({<Date= {"<=$(=Date(addmonths(max(Date),-1),'YYYYMMDD'))"}>} Cost)

Sum from mindate to selected date

Tue, 31 Jan 2012 15:33:20 GMT

The expression works when not selecting any month (it is second last months value), but when selecting, I get 0 value. Any idea?

Sum from mindate to selected date

swuehl — Tue, 31 Jan 2012 16:16:04 GMT

Not yet...

I understood that both expressions work with your sample data and only the sum until last month works not correctly with your data, the other one does, right?

If you don't use a label in your expression, your will get the expression printed as label, but with the set expression evaluated (so you see the formatted date). If you select any month, what Dates do you see in the set expression? Do they seem reasonably (the format should match the format of your Date field)?

Is anything different in your real data model compared to above sample (except Date format)? If so, could you adjust your sample to match it?

Sum from mindate to selected date

Tue, 31 Jan 2012 16:25:59 GMT

Hi,

both are working with the sample, but the latter one (month -1) does not work with my real data.

=sum({<FADate= {"<=20111101"}>}FIXED_ASSETS_COST)

=sum({<FADate= {"<=20111001"}>}FIXED_ASSETS_COST)

Those are the 2 expressions I get when not adding a lable and selecting 20111101, where the first one is just fine and the second one is ok when not selecting a month. The date format seems to be just fine from what I can say.

The only thing that comes to my mind is that there are 3 different tables (1 for calendar data, 1 for invoice data and 1 for general project information data). However, since it's in the expression and not the script, this shouldnt affect (?), and both expressions are ok, eventually.

Re: Sum from mindate to selected date

swuehl — Tue, 31 Jan 2012 16:37:23 GMT

Sorry, no idea yet. Could you upload a small sample?

(Upload is available in advanced editor)

Re: Sum from mindate to selected date

Wed, 01 Feb 2012 11:09:55 GMT

Hi,

I got a bit closer, but don't know how to solve the problem. Attached is a picture (I will try to upload a sample if needed later).

First column should always show max cost of the fixed asset not matter what selections are done. Second column should show total cost to selected period. Third column shows total cost to selected period - 1, and fourth column should just show the change between column 2 and 3. What I've asked for is column 2 and 3.

Anyhow, there are 3 tables, as described above. If I select a period/date from field (reffering to attached pic) "PERIOD_NAME" or "FADate", which both are found in the general infromation table, column 2 is just showing current month value and column 3 is showing 0. I don't mind this since I'm not planning to present either of those fields.

If I select in "Year Month" which is the calendar table, I get the same as above, current month and 0.

If I select from "EffectivePeriod" which is in the invoice table, I get the same again.

However, If I select from "EffectiveDateFromPeriod", which is also in the invoice table, the two expressions are working perfectly fine. This field is also the timefield used in the calendar and the link between those tables.

Any idea how I can get the calendar working instead of the "EffectiveDateFromPeriod" field? No matter where I select in any of the mentioned fields above, Qlikview reduces the data in "EffectiveDateFromPeriod", basically passively selecting in that field. The expressions are however only working when actively selecting in that field.

Re: Sum from mindate to selected date

swuehl — Wed, 01 Feb 2012 11:15:05 GMT

I believe you need to clear all fields that the user might select in and that interfere with EffectiveDateFromPeriod:

sum({<Date= {"<=$(=EffectiveDateFromPeriod (addmonths(max(Date),-1),'YYYYMMDD'))"}, [Year Month]=, EffectivePeriod=, PERIOD_NAME=, FADate= >} FIXED_ASSETS_COSTS)

edit:

corrected some field names, but please double check!