want to display months as numbered rather than jan, feb...

[#1]

Hi all,

I have a table with users and their orders and I have created a column called monthyear.

OrdersbyUser:

LOAD userID,

     userName,

     OrderID,

     Date(OrderDate) as OrderDate,

     MonthName(OrderDate) as monthyear

FROM

ordersbyuser.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

In a table box I have displayed number of orders per user by monthyear (jan 2013, feb 2013... feb 2014).

I want to create a line chart that displays the number of a users orders from their first order to last but instead of sorting it by monthyear(jan 2013, feb 2013 etc), I want it to show their lifetime as users, like month 1, month 2, month 3...

so if a user had made orders in only jan 2013 and another only in feb 2014, they should both be in month 1 and so on...

Thanks in advance for your help!

[MarcoWedel]

Hi Zainab,

for example with these Dates loaded

SET vStartMonth = Date('01.01.2013');
SET vCurrentMonth = Date('01.02.2014');

try this calculation in a textbox: 

=Month($(vCurrentMonth))-Month($(vStartMonth))+(Year($(vCurrentMonth))-Year($(vStartMonth)))*12

hope this helps

regards

Marco

[#3]

Try this as dimension in chart

Round((MonthYear - Min(ALL MonthYear))/30, 1)

It will display the starting month as zero and counts succeeding months from there... so, Aug will be 7 and next year Jan will be 12. You can add + 1 if you want it to be month number.

The same can be implemented through load script as well.

[#4]

I get this error:

Field not found - <varMinDate>

OrdersbyUser:

LOAD userID,

     userName,

     OrderID,

     Date(OrderDate) as OrderDate,

     MonthName(OrderDate) as monthyear,

     'Month' &' '& ($(MonthDiff(varMinDate , OrderDate)) AS MonthsDifference

FROM

ordersbyuser.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[sunilkumarqv]

U missed out below in script

Let  varMinDate= peek('MinDate',0,'MinimumDate');

[#6]

that is a bit closer to what I want, but what if I have a user who made his first order in april 2013, in my chart with this calculated dimension "april 2013" for that user is month 3 and I want it to show as month 1

[tresesco]

Could you please create and share a sample qvw file explaining the expected result? That would take you to the goal faster I guess.

[#8 · Accepted solution]

Instead of 'ALL' in the dimension formula, use 'TOTAL'

So, the formula should change from

=Round((MonthYear - Min(ALL MonthYear))/30, 1))

to

=Round((MonthYear - Min(TOTAL MonthYear))/30, 1))

[#9]

ALL ignores any kind of selection

TOTAL includes selections made in other fields

[#10]

thank you! that shows just what I wanted

[#11]

is there a way to edit your code so that the first month is called month 1 instead of month 0?