Qlik Sense: Combine Quarter with months in a dimension

[rahulgoyal1287]

Hi There,

Thanks for looking into this.

Problem Statement:

I want to create a dimension which would return the values in following format based on algorithm...

Let's assume that today is 10/09/2018

If a quarter is complete then only  it would show as Quarter

if a quarter is not finished then it would only show the month number

(in following summary, since Sep'18 is not finished yet.. we would want to show till Q2 followed by remaining months)

Month_Num	to be calculated in Dimension
1	Q1
2	Q1
3	Q1
4	Q2
5	Q2
6	Q2
7	Jul-18
8	Aug-18
9	Sep-18

I have tried writing an expression which need to be amended to cater above changes:

=If(Month_Num<Ceil(Max(TOTAL Month_Num),3)-2,[CLOSED.autoCalendar.YearQuarter], [CLOSED.autoCalendar.MonthYear])

Thank you for looking into this.

[kaushiknsolanki]

Try to create the same field in script like below.

let vMin = num(MakeDate(2018));

Let vMax = num(today());

Temp:

LOAD Date($(vMin) + RowNo() -1) as Date

AutoGenerate 1

While Date($(vMin) + RowNo() -1) <Date($(vMax));

Master:

Load Date,If(Today() - QuarterStart(Date) >=90,'Q' & Ceil(NUm(Month(Date))/3),MonthName(Date)) as Temp

Resident Temp;

Drop table Temp;

Regards,

Kaushik Solanki

[rangam_s]

Try this.

=if((QuarterEnd(max(saledate))<Today()),QuarterName(max(saledate)),MonthName(max(saledate)))

I have used the saledate as fields for calculating this chart, wherein you can modify the same accordingly.

Or you can store maximum date available in the data into a variable and use it instead of Today() as below

if(QuarterEnd(Max(saledate))<QuarterEnd(Date($(Maximum_Date),'DD/MM/YYYY')),QuarterName(max(saledate)),MonthName(max(saledate)))

[arvind1494 · Accepted solution]

Load Date,If(Today() - QuarterStart(Date) >=90,'Q' & Ceil(NUm(Month(Date))/3),MonthName(Date)) as Temp

Resident table name;

[rahulgoyal1287]

Hi,

I have tried aligning the calculated dimension based on solution provided & it is not working and start giving error -

'Internal engine error'

[rahulgoyal1287]

Thank you Arvind for this fabulous script.. it is working fine...

Best Compliments,

Rahul Goyal