kind of sub select in set analysis

[ali_hijazi]

dears got the following sample data

Date. Amount

1/5/2014. 500

1/12/2014 750

1/19/2014 600

1/26/2014 500

2/5/2014 500

2/12/2014 400

...

thus table is linked to a master calendar what I want is make a chart with month as dimension and the expression to display the sum(amount) but where date is the maximum date of each month

knowing that the data has another column called type so in thesame date I might have several amounts to aum

[tresesco]

Try using calculated dimension like:

Aggr( Max(Date), Month)

If this doesn't help, try to share your sample qvw.

[#3]

Ali,

what may be important...

if for last day of some month you have no any fact row (in your data or after selection) - do you like to show 0, nothing or maybe you would like to show value for last date of that month, where amount exists?

regards

Darek

[trey_bayne]

You could also put Treseco's answer into a Set Analysis String. Use Month as your dimension.

For your expression.

Sum( {$ <Date = { $(=Aggr(Max(Date),Month)) } > } Amount)

[SunilChauhan]

see the attched file

in this max date for Jan is 1/26/2014 and amount for this is 500

and  max date for Feb is 2/12/2014 and amount 400

hope this helps

[trey_bayne]

Sunil. Very elegant solution.

[SunilChauhan]

if its solve you problem then mark it correct or if you have further query please post

Thanks

[#8]

Remamber, that FirstSortedValue() may be not god if you vave more than 1 row for that max date....

regards

Darek

[SunilChauhan]

Great ,thats good to know i have never faced it.

can you please provide a sample for this

thanks

[#10]

Sunil,

As Ali told:

" expression to display the sum(amount) but where date is the maximum date

of each month knowing that the data has another column called type so in

thesame date I might have several amounts to aum"

So, try in your example add rows to have more than 1 for one date, and then

try what result will give your expression

I'am also still not sure what may be master calendar impact. I can only

imagine, that there is more dates in master calendar than in fact table and

maybe Ali is not interested in amout for last day of month but rather last

date where amont exists. This is why i asked Ali to explain this...

Regards

Darek

27-04-2014 09:16 użytkownik "Sunil Kumar Chauhan" <qcwebmaster@qlik.com>

napisał: