How to make bar chart which shows comparison at current year and previous year at product types?

[Anonymous]

Hi,

How to make bar chart which shows comparison at current year and previous year at product types? and if my date datatype is in char variable format how to convert to date format ?

[Anonymous]

For converting character strings to Qlik dates have a look at the Date# function.

[Anonymous]

Hi Bill,

I tried to convert char to date using date# function, but wen I tried to use function as addyear or addmonth ...the value are not changing .

[Anonymous]

Hard to comment without knowing your data and script.

Can you share a sample demonstrating your issue.

[Anonymous]

this the format  of my date in my database.....I have to convert so that I can use in addyear function

[Anonymous]

So that is 7 digits.

• What date would 1998002 convert to ?
• Is it always 7 digits ?

[Anonymous]

it should convert to yyyy -1998 MM-02

[Anonymous · Accepted solution]

The script below using an inline load seems to work.

Data :

Load

Date ( AddYears( Date, -1 )  , 'YYYY-MM' ) as  [Date Previous Year] ,

*

;

Load

Date ( MakeDate( left(StringDate,4) , right(StringDate,2) ) , 'YYYY-MM' )  as Date ,

*

inline

[

StringDate

1998002

1998003

1998004

]

;

[Anonymous]

Hi Bill ,

yes it is working ..I got the current year and previous year data .... But how to apply in one bar chart ...

What expression should i use to show sum of revenue for current year and previous year with the same column name generated in the above script ?

[adj29block]

Hi Napura Gupta,

You use autocalendar Qlik Sense

In bar chart create a rolling 12 months with =[OrderDate.autoCalendar.YearMonth] as Dimension, rename YearMonth

Expression as by Ex:

sum({<[OrderDate.autoCalendar.YearMonth]=, OrderDate= {">=$(=MonthStart(AddMonths(Max(OrderDate), -11)))<=$(=MonthEnd(AddMonths(Max(OrderDate), 0)))"} >}Sales)

I use -11 and 0 for a YearMonth only.

Ex : If I choose May month-12 as June = a period of 12 months.

Title : ='Sales Selection :' & Num(SUM(${<Year={YearMonth}>}Sales),'$#,##0.00;-$#,##0.00')

Filter shutter=[OrderDate.autoCalendar.YearMonth], rename YearMonth

J-B