topic Re: Sales Improvement % by date in QlikView

Sales Improvement % by date

Wed, 13 Aug 2014 01:23:40 GMT

Dear Community,

I would like to write an expression to get % improvement in Sales by date comparison, but my expression seems like not working.

sum({$<Day = {$(=Only(Day))}>}Sales)-sum({$<Day = {$(=Only(Day)-1)}>}Sales)

Anyone could help?

I have attached the qvw file, table in excel sheet is the table that is needed to show on dashboard.

Thanks,

Chanel

Re: Sales Improvement % by date

eduardo_sommer — Wed, 13 Aug 2014 01:57:04 GMT

Hi,

I saw in your data that you don't have consecutive days and day and day-1 compares consecutive days. If day is 8/12/2014, day -1 is 8/11/2014. Your dates are 1 week apart from each other.

Eduardo

Re: Sales Improvement % by date

jagan — Wed, 13 Aug 2014 02:25:23 GMT

Hi Chanel,

PFA file for solution.

Regards,

Jagan.

Re: Sales Improvement % by date

Thu, 21 Aug 2014 09:46:03 GMT

Thanks Jagan! I have tried it out and is working perfectly.

=RangeSum(sum(Sales), -Before(Sum(Sales))) / Alt(Before(Sum(Sales), 1))

This is first time to use rangesum, before and Alt functions, do you mind to briefly explain on this?

Re: Sales Improvement % by date

jagan — Thu, 21 Aug 2014 10:02:44 GMT

Hi,

Please find the explanation below

Rangesum(n1, n2, ....nm) - Will give you sum of n1 + n2 + ......+ nm (Even if one of the value is null you will get the sum for remaining values)

Before() - Will give you preceding column value of the same row

Alt() - will give the default value if expression value is null Ex: Alt(Expression, 'N/A') gives you 'N/A' if Expression returns null.

Hope this helps you.

Regards,

Jagan.

Re: Sales Improvement % by date

Thu, 21 Aug 2014 10:08:31 GMT

Lovely! Appreciate for your help!