Since Last End Day SLED

[#1]

Hi Everyone,

I have a file from which I want to get the amount for the SLED or Since Last End Day

But I can't figure out how to get it ?

I have tried a bunch of stuff that didn't work like the following

Sum({$<[GLDET.GLD_AMT]=P({1<Date={$(=Day(Max([GLDET.GLD_DATE]))}>}[GLDET.GLD_AMT])>}[GLDET.GLD_AMT])

Sum({<Year=, Month=, Day=,[GLDET.GLD_DATE]={">=$(=Num(LastWorkDate(Max([GLDET.GLD_DATE]))))<=$(=Day(Max([GLDET.GLD_DATE])))"}>} [GLDET.GLD_AMT])

so the date comes from [GLDET.GLD_DATE] and the amount comes from [GLDET.GLD_AMT]

I also want the YTD and MTD. I have used the following that did work but I want it to stay as year and as month even if the whole date is selected because currently if in the filter Year is selected then month then day all the 3 columns converts into the same number.

for YTD:

Sum({<Year=, Month=,Day=,[GLDET.GLD_DATE]={">=$(=Num(YearStart(Max([GLDET.GLD_DATE]))))<=$(=Max([GLDET.GLD_DATE]))"}>} [GLDET.GLD_AMT])

for MTD:

Sum({<Year=, Month=,Day=, [GLDET.GLD_DATE]={">=$(=Num(MonthStart(Max([GLDET.GLD_DATE]))))<=$(=Max([GLDET.GLD_DATE]))"}>} [GLDET.GLD_AMT])

Thanks in advance.

Noha

[#2]

I have created a qvf with the data used but I don't know how to attach it here can you tell me how

[#3]

Found It thanks

[sunny_talwar]

Can you check this?

Sum({1<[[GLDET.GLD_DATE]]]={"$(=Num(DayStart(Max([[GLDET.GLD_DATE]]]))))"}>} [[GLDET.GLD_DATE]]])

[#5]

Thank you sunny I believe there is nothing that gives me exactly what I want all the ways give the same result.

I will Check your first answer as correct because this is the equation that calculates the amount the last one you gave is calculating the days value

Thanks again

Noha

[sunny_talwar]

May be if you can specify what your output needs to be based on the sample provided, we might be able to help you better

[#7]

the Column name GLDET.GL_MEMO is the value of last closed month which in our case here will be september

so when you choose in the filter 2016 September I want the MTD to be the only one showing that value

I will make the whole sheet in the Qvf and attach it again

[sunny_talwar]

Yes, that would be very helpful

[#9]

This is the proper Qvf the first one had issues I found that I didn't Load the proper table before attaching it the first time

Sorry for that

Please check this one

Thanks Again

[sunny_talwar]

So when you select September and 2016 MTD should be visible and rest of the two should hide? When will they be visible? What do you see when nothing is selected?

I think hide/show functionality is not get available in Qlik Sense, but I still want to understand what you are after so that I can see if there are any work arounds available here.

Best,

Sunny

[#11]

ok so I have decided to go ahead and use henric idea of flags

Year-over-Year Comparisons

and for sled I found the daily account that closes at the end of the month so I am using that as the account for the sales amount

but now i am stuck in different points.

first I want to do the following

to calculate the SLED or show only the value of AMOUNT if the Date is Max and the account number  = to TF_R_SALES_ACCT

Second I want to calculate   Sum( {$<[GLDET.IsInYTD]={1}>} [GLDET.GLD_AMT])  for the account TF_R_SALES_ACCT

I have tried the following but nothing worked

//SUM(if (([GLDET.GLD_ACCT]= TF_R_SALES_ACCT) & ({$<[GLDET.IsInYTD]={1}>}),([GLTR.GLT_AMT]) ))

//SUM(if (([GLDET.GLD_ACCT]= TF_R_SALES_ACCT),( {$<IsInYTD={1}>}[GLDET.GLD_AMT] ) ))

//Sum( {$<[GLDET.IsInYTD]={1}>} [GLDET.GLD_AMT])

//Sum({$<[GLDET.GLD_AMT]=P({1<[GLDET.GLD_ACCT]={'TF_R_SALES_ACCT'}>}[( {$<[GLDET.IsInYTD]={1}>}[GLDET.GLD_AMT] )])>}[GLDET.GLD_AMT])

//Sum({$<[SUM( {$<[GLDET.IsInYTD]={1}>}[GLDET.GLD_AMT] )]=P({1<[GLDET.GLD_ACCT]={'TF_R_SALES_ACCT'}>}[SUM( {$<[GLDET.IsInYTD]={1}>}[GLDET.GLD_AMT] )])>}[GLDET.GLD_AMT])

//IF (TF_R_COS_ACCT, Sum( {$<[GLDET.IsInYTD]={1}>} [GLDET.GLD_AMT]))