Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
nareshthavidishetty
Creator III
Creator III
‎2017-03-17 01:02 AM

% Change

Hi,

In the attached application i need to introduce % change.

%Change = (2017-2016)/2016

Thanks..

Marketing Scorecard_Newrequest - V2 - Copy (3).qvw
739 Views
0 Likes
1 Solution

Accepted Solutions
sunny_talwar
MVP
MVP
‎2017-03-17 04:51 AM

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

View solution in original post

Marketing Scorecard_Newrequest - V2 - Copy (3).qvw
609 Views
0 Likes
8 Replies
Anil_Babu_Samineni
MVP
MVP
‎2017-03-17 01:10 AM

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)

Or

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
609 Views
0 Likes
nareshthavidishetty
Creator III
Creator III
‎2017-03-17 02:13 AM
Author

In response to Anil_Babu_Samineni

Hi,

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

Untitled.png

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..

609 Views
0 Likes
nareshthavidishetty
Creator III
Creator III
‎2017-03-17 04:11 AM
Author

Hi,

Any clue to get this.

Thanks..

609 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-03-17 04:51 AM

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

Marketing Scorecard_Newrequest - V2 - Copy (3).qvw
610 Views
0 Likes
nareshthavidishetty
Creator III
Creator III
‎2017-03-17 05:08 AM
Author

In response to sunny_talwar

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..

609 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-03-17 05:09 AM

In response to nareshthavidishetty

what do you mean?

609 Views
0 Likes
nareshthavidishetty
Creator III
Creator III
‎2017-03-17 05:45 AM
Author

In response to sunny_talwar

Hi,

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

Thanks..

Marketing Scorecard_Newrequest - V2 - Copy (3).qvw
609 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-03-17 06:58 AM

In response to nareshthavidishetty

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

Marketing Scorecard_Newrequest - V2 - Copy (3).qvw
609 Views