Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum from mindate to selected date

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_IDDateInvoiceCost
12010-10-01A500
12011-03-01B200
2

2011-04-01

C2500
12011-05-01D400
22011-05-01D500
22011-06-01E1000
12011-07-01F300
12011-08-01G600
22011-09-01H1500
12011-11-01I100

 

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!

BR

Robert

11 Replies
swuehl
MVP
MVP

I think you could use a set expression like

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

for this, in combination with a dimension Project_ID.

See also attached.

Not applicable
Author

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.

BR

Robert

swuehl
MVP
MVP

Just add another expression:

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

Regards,

Stefan

Not applicable
Author

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)

BR

Robert

swuehl
MVP
MVP

Date() function should do the job. Try

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

Not applicable
Author

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

swuehl
MVP
MVP

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?

Not applicable
Author

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.

BR

swuehl
MVP
MVP

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

(Upload is available in advanced editor)