Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
deblina_rai
New Contributor II

YTD calculation for each date

Date

Acc

Rep

Other Dims

Trxn Amt

YTD

1/5/2019

AccA

RepX

XXXX

25

25

2/10/2019

AccA

RepX

XXXX

75

100

7/15/2019

AccA

RepX

XXXX

80

180

2/10/2019

AccB

RepY

YYYY

20

20

7/18/2019

AccB

RepY

YYYY

40

60

 

 

I need YTD column to be populated for each row.

such as for 5th jan it will give sales  from 1st jan to 5th  jan

for 2/10/2019 it will give sales from 1st jan to 10 the feb  and so on 

 

Please help me on that

Tags (1)
Labels (2)
13 Replies
Partner
Partner

Re: YTD calculation for each date

Hi,

 

try this:

RangeSum(Above(Sum([Trxn Amt]),0,RowNo()))

deblina_rai
New Contributor II

Re: YTD calculation for each date

Rangesum will not work that i tried. 

 

I need YTD amout for each date

Re: YTD calculation for each date

Why will RangeSum not work? Would you be able to elaborate on the issue you see when you use RangeSum?

deblina_rai
New Contributor II

Re: YTD calculation for each date

i am using this. Can you please help me on the  end date part (underlined)syntax?

 

num(sum({<[Process Date]={">=$(=YearStart(Max([Process Date]))) <=$(=[Process Date]=aggr(nodistinct max([Process Date]),[Firm Name],[Advisory Platform Ind],[TA Alias Rep ID],[Rep First Name],[Rep Last Name],[Rep City],[Rep State Code],[Rep Zip Code],[Acct Num],[TA Prod Code],[Voluntary Tran Desc],[Process Date]))"}

,[Process Month]=>}[Sales Amt]),'#,##0')

Re: YTD calculation for each date

What does this expression has to do with RangeSum? I thought you tried an expression with RangeSum which didnot work for you? or are you considering the below expression to be the RangeSum expression?

deblina_rai
New Contributor II

Re: YTD calculation for each date

for  rangesum   I tried this below:

aggr(rangesum(above( Sum({$<[X529 Special Trade Ind]={'Y'},
[Advisory Platform Ind]-={"=len([Advisory Platform Ind])=0"}>}[Sales Amt]) ,0,rowNo())),[Process Date])

 

 

But the range limit we cant set 0 and  end limit also we cant take rowno() as  it will just sum  up with above column irrespective  of  process date and also after using aggr on  rangesum  it was not working.

 

I need YTD amount for each date in each row.

1st jan to 15th july

1st jan to 16th july   so on like this. SO range sum will not work.

deblina_rai
New Contributor II

Re: YTD calculation for each date

Now  I am using this set analysis. But syntax is not working for end date.

Re: YTD calculation for each date

It will be difficult to give you an exact expression, but you do need to use RangeSum here... Set analysis cannot calculate YTD if you have date as one of the dimension... in order to do that, you need to use RangeSum or AsOfTable 

deblina_rai
New Contributor II

Re: YTD calculation for each date

Rangesum need to set  the range as year start date and end date as that date .

like 5th jan if the date then it will be 1st jan as upper limit and 5th jan as lower limit.  so in this case hoe rangesum will work?