Qlikview Set analsis

[#1]

my data is

creation dateMonth	completion dateMonth	totalsale
Jan-11	Mar-11	10
Feb-11	Mar-11	20
Mar-11	Apr-11	30
Apr-11	Apr-11	40

required out is

monthyear

creation date	sum(totalsale)	sumtotalcompletion
	100	100
Jan-11	10	0
Feb-11	20	0
Mar-11	30	30
Apr-11	40	70

issue: unable to calculate 'sumtotalcompletion' column.

Itried=

Sum(Total {<[Completion dateMont]={'$(=MonthNAme(Max([CATS Creation dateMonth]))'}>}[totalsale])

but not working

PFA sample.

Thanks

Raj

[sinanozdemir]

Hi Raj,

It is not the most elegant way of doing this, but I played with the data model first:

As you can see, I summed totalsale by completion dateMonth and re-named it creation dateMonth. As a result of this, the data model had the below structure:

And in the front end, I simply utilized sum function:

Hope this helps.

Thanks

[#3]

Ya I know above process but my real table is quite big(millions of records), so performing join will affect the load performance. So I am looking a solution by set analysis.