set Analysis Till Selected Period

[ashfaq_haseeb]

Hi All

I'm Facing problem with set Analysis

I have a table like this

LOAD [Fiscal Year],
Year,
[Cost Center],
Object,
Subsidiary,
[Ledger Type],
[Prior Year End Bal],
Subledger,
[Subledger Type],
[Dec Bal],
[Nov Bal],
[oct Bal],
[Sep Bal],
[Aug Bal],
[Jul Bal],
[Jun Bal],
[May Bal],
[Apr Bal],
[Mar Bal],
[Feb Bal],
[Jan Bal],

Month,

if(Month='Jan',1,if(Month='Feb',2,if(Month='Mar',3,if (Month='Apr',4,if(Month='May',5,if(Month='Jun',6,if(Month='Jul',7,if(Month='Aug',8,if(Month='Sep',9,if(Month='Oct',10,if(Month='Nov',11,if(Month='Dec',12)))))))))))) as MonthNumber,
[Account Id]

FROM xyz

now i need calculate for selected perios sum

I acheaved this by following

(sum(num([Jan Bal]))/100+sum(num([Feb Bal]))/100+sum(num([Mar Bal]))/100+sum(num([Apr Bal]))/100+sum(num([May Bal]))/100+sum(num([Jun Bal]))/100+sum(num([Jul Bal]))/100+sum(num([Aug Bal]))/100+sum(num([Sep Bal]))/100+sum(num([oct Bal]))/100+sum(num([Nov Bal]))/100+sum(num([Dec Bal]))/100)

Now I want to calculate sum of month based of highest month selected

i.e if i select Mar

them I should get sum of JAN amount+Feb Amount + MAr Amount.

for this i tried the condition below

=if(Month='Jan',sum(num([Jan Bal])),if(Month='Feb',sum(num([Jan Bal])) + sum(num([Feb Bal])),if(Month='Dec',sum(num([Jan Bal])) + sum(num([Dec Bal])) +sum(num([Feb Bal])))))

but when i select month DEC

it gives me result for december only instead of Jan till Dec

Can Any one Help me on this.

Regards

ASHFAQ

[Oleg_Troyansky]

You would be better off if you could load your balances using CROSSTABLE and have a field "Month" and a field Balance - the whole thing would be much more flexible...

[ashfaq_haseeb]

HI OR

Thanks for the reply man

I acheaved cross table as

if(isnull([Jan Bal]),0,[Jan Bal])+if(isnull([Feb Bal]),0,[Feb Bal])+if(isnull([Mar Bal]),0,[Mar Bal])+if(isnull([Apr Bal]),0,[Apr Bal])+if(isnull([May Bal]),0,[May Bal])+if(isnull([Jun Bal]),0,[Jun Bal])+if(isnull([Jul Bal]),0,[Jul Bal])+if(isnull([Aug Bal]),0,[Aug Bal])+if(isnull([Sep Bal]),0,[Sep Bal])+if(isnull([oct Bal]),0,[oct Bal])+if(isnull([Nov Bal]),0,[Nov Bal])+if(isnull([Dec Bal]),0,[Dec Bal]) as CurrentBal,

now what i have is

i have all the balances in one field now i want to calculate sum till selected month

I tried Below

=sum({<MonthNumber ={"<=$(=(MonthNumber)"}>}CurrentBal)

but no luck

can somebody Please look into this matter

Regards

ASHFAQ

[ashfaq_haseeb]

hi All

Can Someone help me on thiss issue

Thanks

Ashfaq

[mihaiturturica]

I think that you want to calculate YTD to the max selected YearMonth.

Try this:

=num(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} CurrentBal), '#,##0;(#,##0)')

Regards,

Mihai

[ashfaq_haseeb]

HI Mihai Turturica and all

I tried the same

but not getting what i want.

Its gives me zero for any month selected.

Any Idea how can i acheave this

Regards ASHFAQ

[syed_muzammil]

Hi,

Check this post .

http://community.qlik.com/forums/TopicsActive.aspx?ForumID=-1&PageIndex=3&forumoptions=::11::&GroupI...

It has the same requirement as yours.

[mihaiturturica]

Try this. I made a little example for you.

Mihai

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8688.test-crosstable-_2D00_-set-analysis.rar:550:0]