Comparing monthly data

[#1]

Hello everyone,

I am new to Qlikview and have a problem I'm struggling with.

I would like to compare data from the selected month with a previous month.

My data field has been set up as following: LOAD Date(Date, 'MM-YY') as DateAB,

What expression should I use in order to compare data from the selected month with the previous month?

Thanks in advance!

[Anil_Babu_Samineni]

May be this

Sum({<DateAB = {"$(='>=' & (Max(DateAB)-1) & '<=' & Max(DateAB))"}>} Sales)

Or

By Month, Like below

Sum({<DateAB = {"$(='>=' & (Max(Month(DateAB))-1) & '<=' & Max(Month(DateAB)))"}>} Sales)

[PrashantSangle]

Hi,

for current Month

sum({<DateAB={"=$(=max(DateAB))"}>}sales)

for last mont

sum({<DateAB={"=$(=Date(Addmonths(max(DateAB)),-1),'MM-YY')"}>}sales)

Note : careful with date type.

Also serach on community for MTD,LYMTD to get better understing.

Regards,

[#4]

Thank you for your swift response, it is much appreciated!

Your first formula is giving me the wrong information. If I select '11-16' (which is my highest included date) it gives me ALL the previous months combined. I would like to compare '11-16' with '10-16'. So if I select 11-16, it should give me the sum of the field, only for 10-16.

=Sum({<DateAB = {"$(='>=' & (Max(DateAB)-1) & '<=' & Max(DateAB))"}>} [>60 USD])

Second formula

Returns 0.00.

=Sum({<DateAB = {"$(='>=' & (Max(Month(DateAB))-1) & '<=' & Max(Month(DateAB)))"}>} [>60 USD])

It could be my fault that the formula's are not right, what is your input?

I will include all the information about my data field, it is highly possible that I did something wrong:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

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

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

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

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

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

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

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

LOAD Date(Date, 'MM-YY') as DateAB,

num#(Text(Date(Date, 'YYMM'))) as DateMMYY,

[#5]

Thank you for your response!

your first formula indeed gives the value for the current month.

Thought the second formula returns 0.00 for me:

=sum({<DateAB={"=$(=Date(Addmonths(max(DateAB)),-1),'MM-YY')"}>}[>60 USD])

Thought this could be a mistake of me, I will include all the information about my data field, could you please tell me what I'm doing wrong?

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

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

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

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

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

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

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

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

LOAD Date(Date, 'MM-YY') as DateAB,

num#(Text(Date(Date, 'YYMM'))) as DateMMYY,

[Anil_Babu_Samineni]

Then use this for Previous Month of Data after selection, What was the "[>60 USD]"

=Sum({$<DateAB = {"$(=Max(DateAB)-1)"}>} [>60 USD])

[PrashantSangle]

Hi,

I think I have misplaced closing bracket in set analysis.

update set analysis as below

for last month

sum({<DateAB={"=$(=Date(Addmonths(max(DateAB),-1),'MM-YY'))"}>}sales)

also check what was the output of below expression in text object.

=Date(Addmonths(max(DateAB),-1),'MM-YY')

Regards,

[#8]

Thank you for your answer,

If I select '11-16', it gives me the result for 11-16 instead of the previous month (10-16).

The second one returns '10-11' for me if I select '11-16'.

It may also be important to note:

Every document I uploaded into Qv has a separate date.

Document 1: 9-16

Document 2: 10-16

Document 3: 11-16

And so on.

Also, the format that these dates have in excel: '31/07/16', being the last day of the month.

Regards,

[#9]

Thank you for your answer,

The above expression returns a 0.00 for me, [>60 USD] is the field I want the sum for, it are just some dollar amounts.

It may also be important to note:

Every document I uploaded into Qv has a separate date.

Document 1: 9-16

Document 2: 10-16

Document 3: 11-16

And so on.

Also, the format that these dates have in excel: '31/07/16', being the last day of the month.

Regards,

[MK9885]

Calculating Month-over-Month and Yoy Based on current selection

Edit the dimension and also check your Date format from the above link.