regarding Visualization

[GrowingBUG99]

Hi community,

I want to create a bar graph for dimension Month(date)'&'-'&'Year(date) where it displays jan-24 and so on and measure includes count (process) , where for jan-24 it is 25 so on, for e.g i need total showing on the graph as jan 24 , feb 24 and march 24 and then the next bar should have its calculations showing as Q1 assuming which could be 65 in total then after wards apr-24 ,may 24 and jun- 24 then next bar Q2 assuming again could be 95 ....and so on till Q4. 

Thanx in advance

[VBD]

Hi,

if I understand correctly,you can try something like that in your measure : 

if(match(NumMonth,3,6,9,12),RangeSum( Above( Total Sum({1} Total_Price ), 0, 3))):

Regards

[GrowingBUG99]

yes the graph is correctly represented but for the expression it doesn't count for the whole process of Q1, ,Q2 and so on after year and month representation.

[marcus_sommer]

It's not possible with normal period-information from a calendar because a value could be only counted once against the dimension - as month or as quarter but not both at the same time.

To resolve such tasks you need an as-of-dimension which creates the needed n:m relation. More background to the matter is here provided:

The As-Of Table - Qlik Community - 1466130

[JandreKillianRIC · Accepted solution]

Hi @GrowingBUG99 

@marcus_sommer is correct. You need to use a As-Of-Table 

Here is a quick sample just for the quarters. 

DataTmp:
Load * Inline [
Date,Sales
45658,2671
45689,7994
45717,1510
45748,2940
45778,8531
45809,2538
45839,4016
45870,4594
45901,2836
45931,2036
45962,9599
45992,1320
46023,980
46054,5402
46082,7851
];

Data:
Load 
	Date(Date) as Date, 
    Month(Date) as Month, 
    Year(Date) as Year,
    Date(MonthStart(Date),'MMM YYYY') as MonthYear,
    Sales 
Resident DataTmp;

Drop Table DataTmp; 

TmpCal:
Load 
	Num(Min(Date)) as MinDate, 
    Num(Max(Date)) as MaxDate
Resident Data;

Let zMinDate = Peek('MinDate');
Let zMaxDate = Peek('MaxDate');

Drop Table TmpCal; 

Cal:
Load 
	($(zMinDate) + RecNo() - 1) as NewDate
AutoGenerate($(zMaxDate) - $(zMinDate)); 

AsOfTable:
Load
	Distinct
	Date(MonthStart(NewDate), 'MMM YYYY') as MonthYear,
    Date(MonthStart(NewDate), 'MMM YYYY') as AsOfMonth
Resident Cal;

Concatenate(AsOfTable) 
Load 
	'Q'&Ceil(Num(Month(NewDate))/3)&' - '&Year(NewDate) as AsOfMonth,
	Date(MonthStart(NewDate), 'MMM YYYY') as MonthYear
Resident Cal;

Regards Jandre

[diegozecchini]

I would use an AsOfTable too, good idea

[GrowingBUG99]

Thanxx everyone and also @marcus_sommer @JandreKillianRIC , this totally worked for me! 😄 

[JandreKillianRIC]

Hi @GrowingBUG99 

Check out this link that @marcus_sommer shared. This gives a nice in depth overview of the as-of-month concept. 

Regards Jandre

[GrowingBUG99]

Sorry, asking for help again I have a question that might seem silly, but I wanted to ask anyway i was unable to solve the sorting part Q1 Q2 bar wont show after every month for e.g. jan-23 feb-23 mar-23 and Q1 and so on . hope u all dont mind

[JandreKillianRIC · Accepted solution]

Hi @GrowingBUG99 

Sort the As-Of-Month by Expression

=Max(Date) or =Max(MonthYear)

Regards Jandre