topic Re: % Change in QlikView

% Change

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

Hi,

In the attached application i need to introduce % change.

%Change = (2017-2016)/2016

Thanks..

Re: % Change

Anil_Babu_Samineni — Fri, 17 Mar 2017 05:10:39 GMT

I don't have license at this time. View seems you need %Change from (2017 Sales - 2016 Sales)/2016 Sales

Can you post the expression you made to get Data points

(Sum({<Filter1, Filter2, Year = {'2017'}>}Sales)-Sum({<Filter1, Filter2, Year = {'2016'}>}Sales))/Sum({<Filter1, Filter2, Year = {'2016'}>}Sales)

Instead of Year = {'2017'} - You can use your Form like Max(Year).

Re: % Change

nareshthavidishetty — Fri, 17 Mar 2017 06:13:19 GMT

Hi,

Below is the expression used to calculate MTD for last four years.Below is the table structure.

Column 1 Expression:

=Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST)

Thanks..

Re: % Change

nareshthavidishetty — Fri, 17 Mar 2017 08:11:30 GMT

Hi,

Any clue to get this.

Thanks..

Re: % Change

sunny_talwar — Fri, 17 Mar 2017 08:51:41 GMT

May be this

=Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST)

/Above(Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -4)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -4), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST)) - 1

Re: % Change

nareshthavidishetty — Fri, 17 Mar 2017 09:08:23 GMT

Thanks..

What would be the% change logic for the below

=(Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}INVCOUNT))

(Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST))

Thanks..

Re: % Change

sunny_talwar — Fri, 17 Mar 2017 09:09:21 GMT

what do you mean?

Re: % Change

nareshthavidishetty — Fri, 17 Mar 2017 09:45:25 GMT

Hi,

I mean..Closing rate for the chart in attached qvw.

Thanks..

Re: % Change

sunny_talwar — Fri, 17 Mar 2017 10:58:46 GMT

Try this

=(Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}INVCOUNT)

(Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST)))

/Above (Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -4)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -4), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}INVCOUNT)

(Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -4)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -4), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST)))-1