topic Re: Differences based on Date and Sales in QlikView

topic Re: Differences based on Date and Sales in QlikView https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325041#M828281 <HTML><HEAD></HEAD><BODY><P>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.</P></BODY></HTML> Tue, 04 Jul 2017 15:22:30 GMT Anonymous 2017-07-04T15:22:30Z Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325033#M828273 <HTML><HEAD></HEAD><BODY><P>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.</P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/169042_Capture.PNG" style="height: auto;" /></P><P>My script currently looks like this.</P><P><SPAN style="font-size: 14pt;"><BR /></SPAN></P><P><SPAN style="font-size: 14pt;">SET ThousandSep=',';</SPAN></P><P><SPAN style="font-size: 14pt;">SET DecimalSep='.';</SPAN></P><P><SPAN style="font-size: 14pt;">SET MoneyThousandSep=',';</SPAN></P><P><SPAN style="font-size: 14pt;">SET MoneyDecimalSep='.';</SPAN></P><P><SPAN style="font-size: 14pt;">SET MoneyFormat='$#,##0.00;-$#,##0.00';</SPAN></P><P><SPAN style="font-size: 14pt;">SET TimeFormat='hh:mm:ss TT';</SPAN></P><P><SPAN style="font-size: 14pt;">SET DateFormat='M/DD/YY';</SPAN></P><P><SPAN style="font-size: 14pt;">SET TimestampFormat='M/DD/YY hh:mm:ss[.fff] TT';</SPAN></P><P><SPAN style="font-size: 14pt;">SET FirstWeekDay=6;</SPAN></P><P><SPAN style="font-size: 14pt;">SET BrokenWeeks=1;</SPAN></P><P><SPAN style="font-size: 14pt;">SET ReferenceDay=0;</SPAN></P><P><SPAN style="font-size: 14pt;">SET FirstMonthOfYear=1;</SPAN></P><P><SPAN style="font-size: 14pt;">SET CollationLocale='en-CA';</SPAN></P><P><SPAN style="font-size: 14pt;">SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';</SPAN></P><P><SPAN style="font-size: 14pt;">SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';</SPAN></P><P><SPAN style="font-size: 14pt;">SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';</SPAN></P><P><SPAN style="font-size: 14pt;">SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';</SPAN></P><P></P><P><SPAN style="font-size: 14pt;">Report:</SPAN></P><P><SPAN style="font-size: 14pt;">LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]</SPAN></P><P><SPAN style="font-size: 14pt;">(ooxml, embedded labels, table is US);</SPAN></P><P><SPAN style="font-size: 14pt;">concatenate</SPAN></P><P><SPAN style="font-size: 14pt;">LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]</SPAN></P><P><SPAN style="font-size: 14pt;">(ooxml, embedded labels, table is EMEA);</SPAN></P><P><SPAN style="font-size: 14pt;">concatenate</SPAN></P><P><SPAN style="font-size: 14pt;">LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]</SPAN></P><P><SPAN style="font-size: 14pt;">(ooxml, embedded labels, table is CANADA);</SPAN></P><P><SPAN style="font-size: 14pt;">concatenate</SPAN></P><P><SPAN style="font-size: 14pt;">LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]</SPAN></P><P><SPAN style="font-size: 14pt;">(ooxml, embedded labels, table is Canada);</SPAN></P><P><SPAN style="font-size: 14pt;">Concatenate</SPAN></P><P><SPAN style="font-size: 14pt;">LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]</SPAN></P><P><SPAN style="font-size: 14pt;">(ooxml, embedded labels, table is EMEA);</SPAN></P><P><SPAN style="font-size: 14pt;">Concatenate</SPAN></P><P><SPAN style="font-size: 14pt;">LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]</SPAN></P><P><SPAN style="font-size: 14pt;">(ooxml, embedded labels, table is USA);</SPAN></P><P><SPAN style="font-size: 14pt;">Concatenate</SPAN></P><P><SPAN style="font-size: 14pt;">LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]</SPAN></P><P><SPAN style="font-size: 14pt;">(ooxml, embedded labels, table is CanadaApril);</SPAN></P><P><SPAN style="font-size: 14pt;">Concatenate</SPAN></P><P><SPAN style="font-size: 14pt;">LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]</SPAN></P><P><SPAN style="font-size: 14pt;">(ooxml, embedded labels, table is EMEAApril);</SPAN></P><P><SPAN style="font-size: 14pt;">Concatenate</SPAN></P><P><SPAN style="font-size: 14pt;">LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]</SPAN></P><P><SPAN style="font-size: 14pt;">(ooxml, embedded labels, table is USA);</SPAN></P><P></P><P></P><P><SPAN style="font-size: 14pt;">Set vLastMonth="=Date(Max(Month),'MMM-YY')";</SPAN></P><P><SPAN style="font-size: 14pt;">Set v2ndLastMonth="=Date(addMonth(Max(Month),-1),'MMM-YY')";</SPAN></P><P></P><P>And my expression in my table is</P><P></P><P><SPAN style="font-size: 14pt;">Sum({$<Month={"$(vLastMonth)"}>}[Seat Count]) - Sum({$<Month={"$(v2ndlastMonth"}>}[Seat Count])</SPAN></P><P></P><P>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?</P><P>Thanks!</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325033#M828273 Anonymous 2020-11-25T16:16:04Z Re: Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325034#M828274 <HTML><HEAD></HEAD><BODY><P>SUM(Sales) - Above(SUM(Sales))</P></BODY></HTML> Tue, 04 Jul 2017 14:11:23 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325034#M828274 MK_QSL 2017-07-04T14:11:23Z Re: Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325035#M828275 <HTML><HEAD></HEAD><BODY><P>Hey,</P><P>This works for just dates but what about if I wanted to expand it into multiple dimensions, such as below?</P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/169046_Capture.PNG" style="height: 231px; width: 620px;" /></P></BODY></HTML> Tue, 04 Jul 2017 14:20:41 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325035#M828275 Anonymous 2017-07-04T14:20:41Z Re: Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325036#M828276 <HTML><HEAD></HEAD><BODY><P>Provide some more info</P><P>What is dimensions</P><P>What is expression here</P></BODY></HTML> Tue, 04 Jul 2017 14:23:19 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325036#M828276 MK_QSL 2017-07-04T14:23:19Z Re: Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325037#M828277 <HTML><HEAD></HEAD><BODY><P>The dimension in this case is Countries and Dates.</P><P></P><P>The expression is the difference in [Seat Count] from one date to the next for each country.</P><P></P><P>The [Expired] expression in the above screenshot is irrelevant to the Difference calculation as it should only be pertaining to [Seat Count].</P></BODY></HTML> Tue, 04 Jul 2017 14:43:37 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325037#M828277 Anonymous 2017-07-04T14:43:37Z Re: Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325038#M828278 <HTML><HEAD></HEAD><BODY><P>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.</P></BODY></HTML> Tue, 04 Jul 2017 14:47:31 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325038#M828278 Anonymous 2017-07-04T14:47:31Z Re: Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325039#M828279 <HTML><HEAD></HEAD><BODY><P>Can you provide few line of sample data to work?</P></BODY></HTML> Tue, 04 Jul 2017 14:47:44 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325039#M828279 MK_QSL 2017-07-04T14:47:44Z Re: Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325040#M828280 <HTML><HEAD></HEAD><BODY><P>Here's some sample data!</P></BODY></HTML> Tue, 04 Jul 2017 15:20:19 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325040#M828280 Anonymous 2017-07-04T15:20:19Z Re: Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325041#M828281 <HTML><HEAD></HEAD><BODY><P>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.</P></BODY></HTML> Tue, 04 Jul 2017 15:22:30 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325041#M828281 Anonymous 2017-07-04T15:22:30Z Re: Differences based on Date and Sales https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325042#M828282 <HTML><HEAD></HEAD><BODY><P>This worked after I rearranged my dimensions, thanks!</P></BODY></HTML> Tue, 04 Jul 2017 19:59:31 GMT https://community.qlik.com/t5/QlikView/Differences-based-on-Date-and-Sales/m-p/1325042#M828282 Anonymous 2017-07-04T19:59:31Z