topic Re: Convert Number into Date in QlikView

Convert Number into Date

Sun, 20 Sep 2015 01:55:49 GMT

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.

Re: Convert Number into Date

hic — Sun, 20 Sep 2015 10:42:14 GMT

You could try

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

HIC

Re: Convert Number into Date

ecolomer — Sun, 20 Sep 2015 10:56:06 GMT

Or also you can try with:

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

where N = Index from 1 to 18

Re: Convert Number into Date

Sun, 20 Sep 2015 14:35:38 GMT

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

Entire 18 months data should be display.

Re: Convert Number into Date

sunny_talwar — Sun, 20 Sep 2015 14:43:25 GMT

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

Re: Convert Number into Date

MarcoWedel — Sun, 20 Sep 2015 22:02:56 GMT

Hi,

solutions could be also:

hope this helps

regards

Marco

Re: Convert Number into Date

Sun, 20 Sep 2015 23:58:08 GMT

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.

Re: Convert Number into Date

evan_kurowski — Mon, 21 Sep 2015 01:05:51 GMT

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)

Re: Convert Number into Date

Mon, 21 Sep 2015 05:53:34 GMT

Hi,

Go through the below link it might help u.

Convert Number to date format