Re: Calculate data with date condition - Qlik Community

LucyN · ‎2020-02-20

Hi,

I have a table with these columns (field names)

As Of

Entity

Portfolio

Flow Ccy

Ref Ccy

Category

Market

Period

Quarter

Fcst Amount in Disp

When a select a specific Quarter (say 2019-Q4), the Period will be filtered out to 2019-10, 2019-11 and 2019-12. I want to calculate Sum of Fcst Amount for each As Of date when the As Of dates are equal or before the FIRST period of the selected Quarter (in this example, it's 2019-10).

I've attached my sample data below.

JustinDallas · ‎2020-02-20

Do you have any dummy data that we can work with? A simplie Inline statement and a drawing of what you expect given the dummy data should suffice.

LucyN · ‎2020-02-20

Here is the data

LucyN · ‎2020-02-20

Here is the dummy data

As Of	Category	Period	Quarter	Fcst Amount
2019-09	CREVE	2019-10	2019-Q4	1500
2019-10	CREVE	2019-11	2019-Q4	2000
2019-11	CREVE	2019-12	2019-Q4	800
2019-09	REV	2019-10	2019-Q4	1000
2019-10	REV	2019-11	2019-Q4	2500
2019-11	REV	2019-12	2019-Q4	3000
2019-09	CREVE	2019-07	2019-Q3	700
2019-10	CREVE	2019-08	2019-Q3	800
2019-11	CREVE	2019-09	2019-Q3	500
2019-09	REV	2019-07	2019-Q3	500
2019-10	REV	2019-08	2019-Q3	400
2019-11	REV	2019-09	2019-Q3	600

Expectations:

If select 2019-Q4 in my Quarter filter, I would like to find Sum of Forecast Amount of each As Of date which is right on and before the first PERIOD of the selected Quarter.

For example, If I filter by 2019-Q4, the first Period of this Quarter is 2019-10, I want a bar chart which:

- Dimension = As Of (As Of dates <= Period 2019-10)

- Measure = sum of forecast