Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

QlikView - change bar chart dimension label

Hi,

I've attached a qvw sample and its data file.

I have two questions.

1 - How to get a YTD bar chart ?

Example:

1 - If I select jun-2016, in my chart it will show 6 bars for, jan, fev, mar, abr, mai and jun 2016.

2 - If I select fev-2017, in my chart it will show 2 bars for jan and fev 2017.

2 - How to change label for dimension?

Example:

1 - When jan show J

2 - When fev show F

3 - When mar show M

4 - When abr show A

5 - When mai show M

6 - When jun show J

7 - When jul show J

8 - When ago show A

9 - When set show S

10 - When out show O

11 - When nov show N

12 - When dez show D


Problem!!!!!!


When I do this it aggregates all the J, all the A and so on...


But I don't want it to do that, I don't want it aggregating months nor years


How can I achieve this in graph With Calculated Dimension.


Thanks in advanced,


Sílvia

1 Solution

Accepted Solutions
sunny_talwar

Check this out may be

Capture.PNG

View solution in original post

7 Replies
Anonymous
Not applicable
Author

So, I was able to resolve the first question with this expression:

=Sum({<Year=, Month=, MonthYear={">=$(=Date(YearStart(Max(MonthYear)),'MMM-YYYY'))<=$(=Max(MonthYear))"}>} Value)

And using numbers instead of letters in dimension, it works. But my client really wants the letters.

Anyone?

Sílvia

awhitfield
Partner - Champion
Partner - Champion

Hi Sylvia,

you could use a mapping load to add the letter for the month, like the following:

Map:

Mapping

LOAD * INLINE [

    Month, Mth

    jan, J

    fev, F

    mar, M

    abr, A

    mai, M

    jun, J

    jul, J

    ago, A

    set, S

    out, O

    nov, N

    dez, D

];

LOAD

  Year,

     Month,

     ApplyMap ('Map', Month,) as mth,

  Date(MonthYear, 'MMM-YYYY') as MonthYear,

     Metric,

     Value

FROM

[.\Data.xls]

Then use 'mth' as your dimension?

HTH

Best regards

Andy

Anonymous
Not applicable
Author

Hi Andrew,

Thanks for your reply.

I did tried it, but it also shows the months aggregated by letter ( all the A's together, and the J's and M's too)

graph with aggregated letters.png

Sílvia

sunny_talwar

Check this out may be

Capture.PNG

hemanthaanichet
Creator III
Creator III

Hi Salvia,

For First one u can u below

(sum({<Year ={"$(=Date(Date#(Max(MonthYear),'MMM-YYYY'),'YYYY'))"},Month=,MonthYear ={"<=$(=Date(Date#(Max(MonthYear),'MMM-YYYY'),'MMM-YYYY'))"},Metric={'Sales'}>}Value))

Anonymous
Not applicable
Author

And here I was expecting a complex formula, and afterwards, simple as it is.

Thanks Sunny

hemanthaanichet
Creator III
Creator III

for second use the below

LOAD

  Year,

     Month,

     mid(Month,1,1) as MonthStart,

  Date(MonthYear, 'MMM-YYYY') as MonthYear,

     Metric,

     Value

FROM

(biff, embedded labels, table is Sheet1$);