Quarter: start and end units

[spividori]

Hi.

In my application I have the amount at the beginning and end of each period. I need to put together a chart that shows me the amount at the beginning and end of each quarter.
In "Cuadro 1" I get the amount at the beginning of Jan.20 with the following expression: "=Sum({<Periodo={"$(=Min(Periodo))"}>} CantIni)" and in "Cuadro 2" the amount at the end of Dec.20 with the following expression: "=Sum({<Periodo={"$(=Max(Periodo))"}>} CantIni)", but if I use these formulas to show it per quarter, I get the results from "Cuadro Q".
These are the desired results:

Regards.

[chrismarlow]

Hi,

I think you would use ABOVE function, possibly using the set analysis to give you quarters (or possibly you have a calendar table).

If this does not help share a bit more about your data model & might be able to help further.

Cheers,

Chris.

[spividori]

Hi Chris.
Thanks for answering.
I attach a .qvw file with the structure.

Regards.

[chrismarlow]

Hi,

I have personal edition, so won't be able to open what you have sent.

What is your dimension Q? (i.e how is it defined)

Cheers,

Chris.

[spividori]

I attach an excel file with the sample data.

Regards.

[chrismarlow]

Hi,

If you have both Cant Ini/Fin in your dataset I would use FirstSortedValue, like this;

Given you have more than one year I used QuarterName rather than your quarter ... if you have the year selected it probably does not matter.

Cheers,

Chris.

[edwin]

try this.

i used random numbers to generate num field (which is your Cantini), then i used the expressions in 2nd chart to compute for the sum(num) at the start of the quarter and end of the quarter.

i added the field start and end just to show what the start and end values are per month - for illustration

here is the script in case you want to try it (as im using rand num will be different every time you reload)

SET DateFormat='M/D/YYYY';
load *, if(Date=Month,num) as Start, if(Date=MonthEnd,num) as End;
load Date(Date + iterno()-1) as Date,monthstart(Date(Date + iterno()-1)) as Month,  
date(floor(monthend(Date(Date + iterno()-1)))) as MonthEnd, 
QuarterName(Date(Date + iterno()-1)) as Quarter, floor(rand()*10+1) as num while Date(Date + iterno()-1)<=date('12/31/2020');
load date('1/1/2020') as Date AutoGenerate(1);

[spividori]

Hi Chris and Edwin.

Thanks for answering.

This is the expression I need:  "sum(Aggr(If(Min(TOTAL<Trimestre> Periodo) = Periodo, sum(CantIni)),Trimestre, Periodo))" and "sum(Aggr(If(Max(TOTAL<Trimestre> Periodo) = Periodo, sum(CantFin)),Trimestre, Periodo))".

I found it in the following post: https://community.qlik.com/t5/QlikView-App-Dev/How-to-get-the-values-of-the-earliest-latest-month-re...

Regards.

[edwin]

this is the expression you can try:

= Sum(
	Aggr(
		If([Periodo] = Min(TOTAL <[Trimestre]> Periodo), sum(CantIni))
		, [Trimestre], Periodo
	)
)

[edwin]

you cant use a variable as it will work for only one quarter