topic Re: Set analysis in QlikView

Set analysis

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have used the below logic in my chart expression.

(sum({<[Reported Date]={'>=$(vYTDstart)<=$(vYTDend)'}>} TOTAL [Margin Dollars])

sum({<[Reported Date]={'>=$(vYTDstart)<=$(vYTDend)'}>}TOTAL [Margin Booked Sales]))

Here i need to capture only the month end value starting from Feb,2016.

vYTDstart = 02/01/2016

vYTDend=today()-1

Thanks..

Re: Set analysis

MK_QSL — Wed, 25 Jan 2017 09:02:58 GMT

What is the meaning of

Here i need to capture only the month end value starting from Feb,2016.

Re: Set analysis

dsharmaqv — Wed, 25 Jan 2017 09:47:14 GMT

do you mean last day of month ?

Re: Set analysis

sunny_talwar — Wed, 25 Jan 2017 09:49:06 GMT

What are you getting today? I mean what is wrong with the expression you have given above? Not working? Giving wrong results?

Re: Set analysis

rahulpawarb — Wed, 25 Jan 2017 09:59:58 GMT

Hello nareshthavidishetty,

Could you please elaborate more? This will help us to further analyze this issue.

Regards!

Rahul

Re: Set analysis

Anonymous — Wed, 25 Jan 2017 10:06:22 GMT

Do you want your expression to only consider the last day of each month?

i.e values only for these dates:

29/02/2016

31/03/2016

30/04/2016

31/05/2016

30/06/2016

31/07/2016

31/08/2016

30/09/2016

31/10/2016

30/11/2016

31/12/2016

31/01/2017

Re: Set analysis

nareshthavidishetty — Wed, 25 Jan 2017 10:18:22 GMT

Hi,

By default backend values are MTD. I need to get YTD numbers for that if do calculation as

YTD =sum(MTD) which is not correct.

So i need to pick only the monthend numbers stating from Feb 2016.

Thanks..

Re: Set analysis

sunny_talwar — Wed, 25 Jan 2017 10:27:55 GMT

May be this:

(Sum({<[Reported Date]={'>=$(vYTDstart)<=$(vYTDend)'}>*<[Reported Date] = {"=Day([Reported Date]) = Day(MonthEnd([Reported Date]))"}>} TOTAL [Margin Dollars])

Sum({<[Reported Date]={'>=$(vYTDstart)<=$(vYTDend)'}>*<[Reported Date] = {"=Day([Reported Date]) = Day(MonthEnd([Reported Date]))"}>}TOTAL [Margin Booked Sales]))

Re: Set analysis

Wed, 25 Jan 2017 10:40:03 GMT

Hi Sunny ,

Just out of curiosity, you are doing intersection here so it will consider the YTD end as the Month end ?

I could understand the expression partially. Could you say how it functions?

Regards,

Balakrishnan. R

Re: Set analysis

sunny_talwar — Wed, 25 Jan 2017 10:46:13 GMT

Yup,

Look for YTD, but only those Reported Dates which belong to the end of the month dates

Re: Set analysis

Anonymous — Thu, 26 Jan 2017 11:28:37 GMT

DO you need the Day function Sunny. Wouldn't this work,

(Sum({<[Reported Date]={'>=$(vYTDstart)<=$(vYTDend)'}>*<[Reported Date] = {"=MonthEnd([Reported Date])"}>} TOTAL [Margin Dollars])

Sum({<[Reported Date]={'>=$(vYTDstart)<=$(vYTDend)'}>*<[Reported Date] = {"=MonthEnd([Reported Date])"}>}TOTAL [Margin Booked Sales]))

Re: Set analysis

sunny_talwar — Thu, 26 Jan 2017 11:58:43 GMT

I don't think so... but I may be wrong