Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Sum of value prior to selected date range

Im trying to create an expression that sums up a value but for everyhting prior to the selected dates.

For instance if I select Q1 of 2013 and get $1000 for the sum of the selection, I need to show what I got before this.

I've easily gotten set analysis to work for year by year comparison, but Im stuck on this. The best I was taking all "{1} - {$}" on the date field, but that then included data that did not pertain to the selected date range, but only because it had values that feel in the all range.

Does anyone have a sample?

Sum({<Field_Date={" HELP ME HERE "}>} Total_Amount)

7 Replies
Not applicable

Re: Sum of value prior to selected date range

Hi,

Try with this:

Sum ({< Field_Date={"<$(=Max(Field_Date))"} >} TOTAL_AMOUNT)

Regards,

Ricardo

Not applicable

Re: Sum of value prior to selected date range

I tried using "Max" and would just return the same totaled amount. So I tried "Min" since I wanted earliest date and then get everything before that and I get zeros while I know there are values

Not applicable

Re: Sum of value prior to selected date range

Well, I need more details about your app. I have an idea. Can you share your app?

If It has a lot of rows, reduce the data.

File -> Reduce Data -> Keep Possible Values

Regards,

Ricardo

Not applicable

Re: Sum of value prior to selected date range

The data set is tiny, probably 20,000 rows. I cannot share the app due it would violate company policy.

MVP
MVP

Re: Sum of value prior to selected date range

Hi,

Try this

=Sum({<Field_Date={'<=$(=Max(Field_Date)'}>} Total_Amount)

If you have any Date filters like Year, Month, Quarter or week then try this

=Sum({<YearDimensionName=, MonthDimensionName=, QuarterDimensionName=, WeekDimensionName=, Field_Date={'<=$(=Max(Field_Date)'}>} Total_Amount)

The above experssion ignores selections of Month, Year, Quarter and Week dimensions.

Regards,

Jagan.

Not applicable

Re: Sum of value prior to selected date range

Jagan,

Thank you, this has worked to some degree. I did have date filters and by ignoring the selection I was able to get to sum up the amount by using the less than of the Min value of the date. However by ignoring the selections, I now have added more rows of the data to my Straight Table chart than needs to be.

MVP
MVP

Re: Sum of value prior to selected date range

Hi,

Can you explain what you exactly need when any Date filters are selected.

Regards,

Jagan.

Community Browser