Solved: Bar chart with calculated dimension for (M & YTD) - Qlik Community

Report Inappropriate Content · ‎2011-03-02

My objective is to build a 100% stacked bar chart with 2dimension and 1expression. In x Axis, I would find [Current Month], [YTD], [YTD-1] (so, I can compare the repartition over the time).

I guess I need to create a calculated dimension for that; but how will my observations of the current month, being counted twice for [Current Month] and [YTD]?

Report Inappropriate Content · ‎2011-03-08

I finally followed the example mentioned here:

http://community.qlik.com/forums/t/32982.aspx

Here is my adjusted script (I am dealing with Date which are always the 1st day of the month):

Dates:
Load Distinct OpenMonth as Date
Resident Fact;

OpenAsOf:
LOAD
"Date" as "As of OpenMonth"
,'YTD' as "OpenMonth Range"
,MonthName("Date", - iterno() + 1) as "OpenMonth"
RESIDENT Dates
WHILE year("Date") = year(MonthName("Date", - iterno() + 1))
;
CONCATENATE (OpenAsOf)
LOAD
"Date" as "As of OpenMonth"
,'YTD-1' as "OpenMonth Range"
,MonthName("Date", - iterno() -11) as "OpenMonth"
RESIDENT Dates
WHILE year("Date")-1 = year(MonthName("Date", - iterno() -11))
;
CONCATENATE (OpenAsOf)
LOAD
"Date" as "As of OpenMonth"
,'M-1' as "OpenMonth Range"
,MonthName(Date,-1) as "OpenMonth"
RESIDENT Dates
;
CONCATENATE (OpenAsOf)
LOAD
"Date" as "As of OpenMonth"
,'M' as "OpenMonth Range"
,"Date" as "OpenMonth"
RESIDENT Dates
;
DROP TABLE [Dates]
;

View solution in original post

brenner_martina · ‎2011-03-02

Hi,

best way is, to create a table as calender in the script with all needed datefunctions and combine it to your datefield in data. Than you dont need to create a dynamic dimension (what of course is possible).

Report Inappropriate Content · ‎2011-03-02

Thanks for the quick reply. I am trying to picture your idea but I am still new in QV.

On another object, I use following expressions:

For observation of the month: count( { $ < OpenMonth = {'$(#v_dateselection)'} >} [SD])

For observation of the YTD: count( {$ <OpenMonth = {"=NUM(OpenMonth) <= $(=NUM(v_dateselection))"}, OpenYear={$(=YEAR(v_dateselection))}> }SD)

where v_dateselection is the a date format

Now, in OpenMonth, I have thousand of different dates, so how am I going to build a 'calender' table? Would I need a column OpenMonth to make a link with my fact table? Should this column contain all distinct value of OpenMonth from my fact table?

I can't figure out the format of your calender table. It is empty? All columns are only build from expression based on the date?

Thanks in advance,

Report Inappropriate Content · ‎2011-03-02

<body><p>For the moment, I load a 'calendar table' like this:</p> <p>As you can see, I have duplicated Feb-11 in order that my data appears for my stat M and YTD. </p> <p>So after I can use the column 'Stat' has a dimension of my chart. </p> <p>It is a correct approach? Is there more straight one?</p> <p> </p> <table border="0" cellpadding="0" cellspacing="0" width="138"> <p><col style="width:56pt;" width="74"></col> <col style="width:48pt;" width="64"></col> <tbody> <tr style="height:12.75pt;"> <td class="xl24" style="height:12.75pt;width:56pt;" height="17" width="74">OpenMonth</td> <td class="xl24" style="border-left:medium none;width:48pt;" width="64">Stat</td> </tr> <tr style="height:12.75pt;"> <td class="xl25" style="height:12.75pt;border-top:medium none;" align="right" height="17">1/1/2010</td> <td class="xl24" style="border-top:medium none;border-left:medium none;">YTD-1</td> </tr> <tr style="height:12.75pt;"> <td class="xl25" style="height:12.75pt;border-top:medium none;" align="right" height="17">2/1/2010</td> <td class="xl24" style="border-top:medium none;border-left:medium none;">YTD-1</td> </tr> <tr style="height:12.75pt;"> <td class="xl25" style="height:12.75pt;border-top:medium none;" align="right" height="17">1/1/2011</td> <td class="xl24" style="border-top:medium none;border-left:medium none;">YTD</td> </tr> <tr style="height:12.75pt;"> <td class="xl25" style="height:12.75pt;border-top:medium none;" align="right" height="17">2/1/2011</td> <td class="xl24" style="border-top:medium none;border-left:medium none;">YTD</td> </tr> <tr style="height:12.75pt;"> <td class="xl25" style="height:12.75pt;border-top:medium none;" align="right" height="17">2/1/2011</td> <td class="xl24" style="border-top:medium none;border-left:medium none;">M</td> </tr> </tbody> </p> <p> </p> </table> <p> </p> <p> </p></body>

Report Inappropriate Content · ‎2011-03-04

This is the output I am getting with the solution mentioned above.

The output is what I want, but the solution is not. I don't mind to load an additional table, but not in the format mentioned above (because it serves only this need) and dirty the data model.

Report Inappropriate Content · ‎2011-03-08

I finally followed the example mentioned here:

http://community.qlik.com/forums/t/32982.aspx

Here is my adjusted script (I am dealing with Date which are always the 1st day of the month):

Dates:
Load Distinct OpenMonth as Date
Resident Fact;

OpenAsOf:
LOAD
"Date" as "As of OpenMonth"
,'YTD' as "OpenMonth Range"
,MonthName("Date", - iterno() + 1) as "OpenMonth"
RESIDENT Dates
WHILE year("Date") = year(MonthName("Date", - iterno() + 1))
;
CONCATENATE (OpenAsOf)
LOAD
"Date" as "As of OpenMonth"
,'YTD-1' as "OpenMonth Range"
,MonthName("Date", - iterno() -11) as "OpenMonth"
RESIDENT Dates
WHILE year("Date")-1 = year(MonthName("Date", - iterno() -11))
;
CONCATENATE (OpenAsOf)
LOAD
"Date" as "As of OpenMonth"
,'M-1' as "OpenMonth Range"
,MonthName(Date,-1) as "OpenMonth"
RESIDENT Dates
;
CONCATENATE (OpenAsOf)
LOAD
"Date" as "As of OpenMonth"
,'M' as "OpenMonth Range"
,"Date" as "OpenMonth"
RESIDENT Dates
;
DROP TABLE [Dates]
;