Convert Number into Date

[#1]

Hi There

I have requirement like below.

Dimension: Month - I am getting number from 0 ,- 1, -2 , -3  ...-17. ( last 18 months of data).

In chart i am using  this dimension and Expression for sum(Total).

I am seeing x axis  dimensions as 0, -1,-2 .. -17.  Instead of that i want to display last 18 months in MM YYYY format.

( 0 = Current month , -1= (Current month -1)

Thanks in advance.

[hic]

You could try

  Date(AddMonths(MonthStart(Today()),<YourMonthNumber>), 'MM YYYY')

HIC

[ecolomer]

Or also you can try with:

MonthName(AddMonths(MonthName(Today()),-N), 'MM YYYY')

where N = Index from 1 to 18

[#4]

Thanks for the quick reply, but i dont want to display one month,

Entire 18 months data should be display.

[sunny_talwar]

If you use Henric‌'s recommendation or Enrique‌'s recommendation as a calculated dimension, you should be able to get all the dates in your chart. The difference is the Henric's solution will give you all the dates and Enrique's will give you data aggregated for each MonthYear.

You can also use their recommendation in the script to create the dimension in the script itself.

Hope this helps.

Best,

Sunny

[MarcoWedel]

Hi,

solutions could be also:

hope this helps

regards

Marco

[#7]

Thanks for the updates, I have IDNo as primary key and data associated with this ID.

e.g, For IDNo: 100100 , i am getting only 5 months of data , but in the graph i want to all previous 18 months.

hence , the for the month where data is not present it will show blank in graph.

[evan_kurowski]

Hello Ashish,

In a chart made from any of the examples provided, try unchecking 'Suppress Zero-Values'.  This should show a bar plot for every dimension, even if there aren't any fact rows for a particular month (there must however at least be an entry for the dimension in the dimension table)

[#9]

Hi,

Go through the below link it might help u.

Convert Number to date format