topic Re: Since Last End Day SLED in QlikView

Since Last End Day SLED

Wed, 26 Oct 2016 20:19:09 GMT

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

Re: Since Last End Day SLED

sunny_talwar — Wed, 26 Oct 2016 20:28:04 GMT

By means of an example, can you show what SLED means? I understand it is short name for Since Last End Day, but not sure what date range it will corresponds to?

Re: Since Last End Day SLED

Anonymous — Wed, 26 Oct 2016 20:29:57 GMT

may be check with your date format and include "1"

Sum( 1 {<Year=, Month=,Day=,[GLDET.GLD_DATE]={">=$(=Date(YearStart(Max([GLDET.GLD_DATE]))),’MM/DD/YYYY’)<=$(=Date(Max([GLDET.GLD_DATE]),’MM/DD/YYYY’)"}> } [GLDET.GLD_AMT] )

Re: Since Last End Day SLED

Wed, 26 Oct 2016 20:35:32 GMT

it should be since yesterday closure till today

Re: Since Last End Day SLED

Wed, 26 Oct 2016 20:42:19 GMT

Thank you for your help but that gave me dashes

my formula I wrote gives me the following table

but as you can see all have the same value when there is no selection made in the filter and also gives me the same look when I choose the year month and day in the filter

Re: Since Last End Day SLED

sunny_talwar — Wed, 26 Oct 2016 20:42:42 GMT

Maybe something like this:

Sum({1<Date={"$(=Date(DayStart(Max([GLDET.GLD_DATE])), 'DateFieldFormatHere'))"}>} [GLDET.GLD_AMT])

Replace the placeholder for date format with your date field format (DateFieldFormatHere)

Re: Since Last End Day SLED

Wed, 26 Oct 2016 21:46:55 GMT

Thanks sunny but It is showing no movement even if day changed

Re: Since Last End Day SLED

sunny_talwar — Wed, 26 Oct 2016 22:34:28 GMT

Which day changed? Where are you making a selection?

Re: Since Last End Day SLED

Thu, 27 Oct 2016 14:00:18 GMT

I am saying it didn't change it cancelled the selections but what I am hoping is that if they today selected for example 25 of april 2016 the SLED will show the the day 24-25 and the MTD shows the from 1-25 and the year will show from Jan- april

Re: Since Last End Day SLED

sunny_talwar — Thu, 27 Oct 2016 14:16:17 GMT

That is what it should do. Not sure what isn't working. Would you be able to share a sample?

Re: Since Last End Day SLED

Thu, 27 Oct 2016 14:32:19 GMT

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

Re: Since Last End Day SLED

Thu, 27 Oct 2016 14:33:51 GMT

Found It thanks

Re: Since Last End Day SLED

sunny_talwar — Thu, 27 Oct 2016 15:11:47 GMT

Can you check this?

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

Re: Since Last End Day SLED

Thu, 27 Oct 2016 15:27:20 GMT

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

Re: Since Last End Day SLED

sunny_talwar — Thu, 27 Oct 2016 15:29:13 GMT

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

Re: Since Last End Day SLED

Thu, 27 Oct 2016 15:38:22 GMT

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

Re: Since Last End Day SLED

sunny_talwar — Thu, 27 Oct 2016 15:40:09 GMT

Yes, that would be very helpful

Re: Since Last End Day SLED

Thu, 27 Oct 2016 15:48:30 GMT

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

Re: Since Last End Day SLED

sunny_talwar — Thu, 27 Oct 2016 20:12:24 GMT

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

Re: Since Last End Day SLED

Fri, 28 Oct 2016 17:16:11 GMT

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]))