topic Re: Qlik Sense: Combine Quarter with months in a dimension in App Development

Qlik Sense: Combine Quarter with months in a dimension

rahulgoyal1287 — Tue, 19 Jun 2018 08:11:36 GMT

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.

Re: Qlik Sense: Combine Quarter with months in a dimension

kaushiknsolanki — Tue, 19 Jun 2018 08:24:50 GMT

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

Re: Qlik Sense: Combine Quarter with months in a dimension

rangam_s — Tue, 19 Jun 2018 11:49:54 GMT

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)))

Re: Qlik Sense: Combine Quarter with months in a dimension

arvind1494 — Tue, 19 Jun 2018 11:57:18 GMT

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

Resident table name;

Re: Qlik Sense: Combine Quarter with months in a dimension

rahulgoyal1287 — Thu, 21 Jun 2018 04:25:40 GMT

Hi,

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

'Internal engine error'

Re: Qlik Sense: Combine Quarter with months in a dimension

rahulgoyal1287 — Thu, 21 Jun 2018 04:31:17 GMT

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

Best Compliments,

Rahul Goyal