topic Compare current sum Rev VS last year sum Rev in QlikView

Compare current sum Rev VS last year sum Rev

wassimharfoush — Wed, 24 Sep 2014 17:31:44 GMT

I am trying to compare the sum of Revenue for period based on current selection of a DateField VS The same period for

previous year and I am using the expression for previous year period:

Sum({<Datefield={'$(=addyears(DateField-1))'}>}Rev)

That works when the user select a single period but doesn't work when the user select multi period.

Re: Compare current sum Rev VS last year sum Rev

Anonymous — Wed, 24 Sep 2014 17:43:22 GMT

Sum({<Datefield={" >= $(=addyears(Min(DateField), -1)) <= $(=addyears(Max(DateField), -1))"}>}Rev)

Re: Compare current sum Rev VS last year sum Rev

Anonymous — Wed, 24 Sep 2014 18:28:17 GMT

Try this expression:

Sum({$+<[Fiscal Year]={"$(=AddYears([Fiscal Year],0)-1)","$(=AddYears(Min([Fiscal Year]),0)-1)"}>}Rev)

If you don't want to show them without a selection then you need to active the conditional expression check box and add this expression

GetSelectedCount([Fiscal Year])>0

Regards

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Wed, 24 Sep 2014 22:36:57 GMT

None of the above work.. Any other suggestions?

Best,

Alec

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 01:27:15 GMT

Hi Waild,

the fiscal year field that you use, is it a year column? or full date? The solution doesn't work on my end.

Best,

Alec

Re: Compare current sum Rev VS last year sum Rev

Anonymous — Thu, 25 Sep 2014 01:30:20 GMT

This one should work

Sum({$<DateField={">= $(=addyears(Min(DateField),0) -1) <= $(=addyears(Max(DateField),0) -1)"}>}Rev)

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 01:38:04 GMT

Thank you for your help but still not working..

Re: Compare current sum Rev VS last year sum Rev

Anonymous — Thu, 25 Sep 2014 01:52:49 GMT

It is a year field. You might need to use Makedate function before addyears if you apply it on a full date. So try it with a year column

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 02:08:22 GMT

I also tried the following with no luck:

=Sum({$<RDATEValue={">= $(=addmonths(Min(RDATEValue),0) -12)"," <= $(=addmonths(Max(RDATEValue),0) -12)"}>}REV)

it is not returning error but it returns 0 which means the expression is correct but not sure why it returns 0.

Any help is appreciated

Re: Compare current sum Rev VS last year sum Rev

Anonymous — Thu, 25 Sep 2014 02:18:04 GMT

Is RDATEValue a month column?

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 02:20:25 GMT

it is a date field with format as 09/01/2014 and so on back to 3 years

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 03:06:36 GMT

to simplify the issue and understand what's wrong I used the following expression:

=sum( {$<RDATEValue={"$(=(AddMonths(min([RDATEValue]),-12)))"}>} REV)

Which returns the rev of the minimum period and it works .

Now when I add the greater than symbol ">" it returns 0.

=sum( {$<RDATEValue={"$(>=(AddMonths(min([RDATEValue]),-12)))"}>} REV)

Hope someone can help

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 03:49:32 GMT

Hi guys,

the following made it for me using Min and Max

num(sum( {$<RDATEValue={">=$(=(AddMonths(min([RDATEValue]),-12))) <=$(=(AddMonths(max([RDATEValue]),-12)))"}>} REV))

but what if the user makes few selections on the RDATEValue field and they are not in order?

meaning what if the user select 09/01/2014 and 06/01/2014 and 01/01/2014 We need to get the REV for the same periods from the previous year..

Let me know your thoughts..

Re: Compare current sum Rev VS last year sum Rev

ashishkalia — Thu, 25 Sep 2014 04:15:39 GMT

hies alec1982,

Greetings.

My suggestion is that let the user do any selections on date field but for your computations, calculate it on the variables
eg. user selects 20/8/2014 as the max date for period
so go to the variable and calculate it as vMaxPeriodDate=addmonths(max(RDATEvalue),-12)
do same with the min period date
and then introduce it in your expression as

=sum( {<RDATEValue={'<=$(vMaxPeriodDate)'},RDATEValue={'<=$(vMinPeriodDate)'}>} REV);

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 04:44:55 GMT

Thank you for the update but your input doesn't fix the issue I am having.

The issue is using the following expression

num(sum( {$<RDATEValue={">=$(=(AddMonths(min([RDATEValue]),-12))) <=$(=(AddMonths(max([RDATEValue]),-12)))"}>} REV))

The user will get the REV for all periods in between the min and max values he selects.

What if the user select three dates as follow 09/01/2013, 07/01/2013 and 01/01/2014 the result I am looking for is the REV for only the three periods for the previous year while the expression that i have and the one that you provided return the REV for all periods between Min and Max dates and not only the three periods the user selected.

Best,

Alec

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 14:08:06 GMT

Wondering if anyone can help?

Best,

Alec

Re: Compare current sum Rev VS last year sum Rev

muniyandi — Thu, 25 Sep 2014 14:16:28 GMT

Hi ,

if possible ,Can you post sample file.

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 15:14:47 GMT

Here we go .. I have attached a sample

Re: Compare current sum Rev VS last year sum Rev

alec1982 — Thu, 25 Sep 2014 18:16:03 GMT

Hi Mathew,

Have you had a chance to look at the sample?

Best,

Alec

Re: Re: Compare current sum Rev VS last year sum Rev

muniyandi — Thu, 25 Sep 2014 19:11:58 GMT

Hi Alec,

Kindly verify my attachment file. Check Variable test name

i hope this will help to u.

Thanks,

Muni !