Qlikview Identifier Issue for sum of values over last 12 month

[Anonymous]

Hi

I’m still a bit of a beginner with QlikView expressions and am probably missing something obvious here. Any help pointing out where I’m going wrong will be gratefully accepted.

As part of an application I’m building I need to display a chart that sums the number of approved projects over the last 365 days split by region. This is for comparative purposes and needs to be unaffected by any other selections made in the application.

Using the expression below and ‘Region’ as the dimension the chart displays correctly as long as no other selections are made, but other selections change what is displayed. The expression is limiting the data to within the last 365 days, so if for instance a selection is made in the dashboard for an event that pre-dates the year-ago date the chart is empty, and if a selection is made for an event within the last 365 days the chart will only display the data for that specific date.

This is the expression I’m using:

Sum({1<Status={'Approved'},App_Close_Date={"=App_Close_Date>=Date_Year_Ago"}>}Activity_Count)

[YoussefBelloum]

Hi,

you can dissociate fields selections from an expression using set analysis like this:

1. your example:

Sum({1<Status={'Approved'},App_Close_Date={"=App_Close_Date>=Date_Year_Ago"}>}Activity_Count)

==> 1 means all the model, so here your chart will not move when you filter data using all the fields.

2. dissociate 1 or more fields from the expression:

Sum({<FIELD_1=, FIELD_2=,Status={'Approved'},App_Close_Date={"=App_Close_Date>=Date_Year_Ago"}>}Activity_Count)

==> here FIELD_1 and FIELD_2 will be dissociated from your expression.

are you looking to do something more specific ?

[Anonymous]

Hi Youssef

My chart does not move when I select a specific region (the chart dimension), but does when I select a specific App_Close_Date. What I'm looking for is a way to lock this so it's not changed when I select a specific App_Close_Date to compare with the sum of the last year.

[YoussefBelloum]

App_Close_Date field is part of a disconnected table ?

Also, would you be able to share sample app ?

[Anonymous]

The app  loads data directly from a database and includes confidential data, so not easy to upload here with dummy data. The image here is a snip of the internal table relationships for the tables this chart uses - hopefully this helps to clarify the issue.

Region_Name is the dimension and Activity_Count is the measure. I'm trying to lock the chart so it always displays all values where Application_Close_Date is greater than Date-Year-Ago. The primary intended use for this is to be able to select an individual Funding_Round and see charts for both the total sum of Activity_Count split by Region_Name for the last year for all Funding_Rounds with an Application_Close_Date greater than Date_Year_Ago and another chart that just shows this detail for the selected Funding_Round.

[Anonymous]

I've found a way to get this to work. I've added the following line to the Load script;

IF((Date#("Application_Close_Date",'DDMMYYYY')>FLOOR(Date#(Today())-365)),'True') AS [Within_last_year],

and changed the chart expression to;

Sum({1<Status={'Approved'},[Within_last_year]={'True'}>}Activity_Count)

The chart now displays the totals for everything for the last 365 days and does not change when I select a single Funding-Round to compare with this. Not sure if this is the tidiest solution, but it works.

[YoussefBelloum]

Ok I see

before trying what you will see below, make sure that your App_Close_Date and Date_Year_Ago fields have the same date format.

Try this:

=if(App_Close_Date>=Date_Year_Ago, Sum({1<Status={'Approved'}>}Activity_Count))

[YoussefBelloum]

actually, it's a workaround, because you should be able to compare two dates in an expression, either via an IF or via a set analysis

But it is good if it's working, try anyway the expression that I sent you, I like to have several alternatives for each problem