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!

[#2]

Can you not have the monthyear as the dimension, with Count(Username) as the expression ?

[SunilChauhan]

use

'Month' &' '&num(month(OrderDate)) as FieldName

in above script

hope this helps

[#4]

then it shows month as "jan 2013", I want to show month 1, month 2 etc

[jagan]

Hi,

Try like this

LOAD userID,

     userName,

     OrderID,

     Date(OrderDate) as OrderDate,

     MonthName(OrderDate) as monthyear,

     'Month' &' '&month(OrderDate) *1 AS MonthNumber

FROM

ordersbyuser.csv

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

Regards,

jagan.

[#6]

there is one problem with your code though, it shows both jan 2013 and jan 2014 as month 1 when jan 2014 should be month 13

[sunilkumarqv]

Hi Zainab,

you can make conditional Dimension in your chart ,As of now you taken monthyear as Dimension

simply change monthyear =month(monthyear )  in your conditional dimension and lable it as Month.

hope this surly help you !

[#8]

that would work if I had only one year but I have 14 months from jan 2013 to feb 2014 and with your code, it shows both jan 2013 and jan 2014 as month 1

[jagan]

Hi,

Try like this

//Function to calculate Months Difference in script

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

MinimumDate:

LOAD

Min(OrderDate) AS MinDate

FROM

ordersbyuser.csv

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

LET varMinDate = Date(Peek('MinDate',0));

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);

Regards,

jagan.

[sunilkumarqv]

In Dimesion tab ,conditional Dimension use below

=NumCount(Month(monthyear)) in your dimension tab conditinal dimesion  and lable it as Month .

it gives what you want