Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Differences based on Date and Sales

Hey guys, I'm trying to create visuals that show differences from a month to month in a single metric. Similar to what is happening in this sample table I made in excel.

Capture.PNG

My script currently looks like this.


SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='M/DD/YY';

SET TimestampFormat='M/DD/YY hh:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-CA';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Report:

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is US);

concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is EMEA);

concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is CANADA);

concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is Canada);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is EMEA);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is USA);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is CanadaApril);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is EMEAApril);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is USA);

Set vLastMonth="=Date(Max(Month),'MMM-YY')";

Set v2ndLastMonth="=Date(addMonth(Max(Month),-1),'MMM-YY')";

And my expression in my table is

Sum({$<Month={"$(vLastMonth)"}>}[Seat Count]) - Sum({$<Month={"$(v2ndlastMonth"}>}[Seat Count])

How would I go about doing what I need? Should I be using a master calendar and, if so, how do I format my expression using a Calendar to give me the results I need?

Thanks!

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

SUM(Sales) - Above(SUM(Sales))

View solution in original post

9 Replies
MK_QSL
MVP
MVP

SUM(Sales) - Above(SUM(Sales))

Anonymous
Not applicable
Author

Hey,

This works for just dates but what about if I wanted to expand it into multiple dimensions, such as below?

Capture.PNG

MK_QSL
MVP
MVP

Provide some more info

What is dimensions

What is expression here

Anonymous
Not applicable
Author

The dimension in this case is Countries and Dates.

The expression is the difference in [Seat Count] from one date to the next for each country.

The [Expired] expression in the above screenshot is irrelevant to the Difference calculation as it should only be pertaining to [Seat Count].

Anonymous
Not applicable
Author

The Object to the left is the table I am trying to create. The object to the right is the test object I made using you initial response.

MK_QSL
MVP
MVP

Can you provide few line of sample data to work?

Anonymous
Not applicable
Author

Here's some sample data!

Anonymous
Not applicable
Author

Would it be smarter to just create a new Excel column using DAX arithmetic to do this? Seems like QlikView doesn't support this type of expression in a way that makes it worth doing in their program instead of in the file that's being imported.

Anonymous
Not applicable
Author

This worked after I rearranged my dimensions, thanks!