Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

dandaanilreddy
Contributor III

year and quarter

!Hwllo Qlik Developers

I have a pivot table in which i have 3 dimensions year, quarter and Date.

I want to display data from 2015 last quarter to 2017 2nd quarter. For the date dimension i wrote the calculated dimension as

=if(MonthName(UnitActuals.ReportedDate) >= addmonths(today(),-13)

and MonthName(UnitActuals.ReportedDate) <= addmonths(today(),+6), MonthName(UnitActuals.ReportedDate))

i tried the same way for year and quarter but its not working. Can someone help me please.

Thanks

Anil

13 Replies
sangeess21
Contributor

Re: year and quarter

Try this. This would work if UnitActuals.ReportedDate is in date format

=if(UnitActuals.ReportedDate >= addmonths(today(),-13) and UnitActuals.ReportedDate<= addmonths(today(),6), MonthName(UnitActuals.ReportedDate))

dandaanilreddy
Contributor III

Re: year and quarter

That is what i wrote to get month, i want the same logic for the year and quarter fields.

Thanks

Anil

Khan_Mohammed
Honored Contributor II

Re: year and quarter

It is quite tough to achieve that as a field.

What you can do is write a set expression taking your months field and only take last quarter from 2015, year =2016 and year =2017, Q2.

Expression might look like this

Sum({<Year = {2015}, Quarter = {'Q4}>+< Year={2016}, Quarter>+< Year={2017}, Quarter={'Q1','Q2'} }>}Yourdimension)

I'm not sure if this will work or not...but you'll have to do this in front end.

In script you can create Flags for quarter or month.

Khan_Mohammed
Honored Contributor II

Re: year and quarter

if(InQuarter(TempDate, today(),+1), 1, 0) as FutureQ1trFlag, <<< Q1 Flag for 2017

if(InQuarter(TempDate, today(),+2), 1, 0) as FutureQ2trFlag, <<<<Q2 Flag for 2017

if(InQuarter(TempDate, today(),-4), 1, 0) as PYQtrFlag, <<<<Q4 Flag for 2015

And 2016 you can use full Quarter as field, no need to use flags.

Sum({<FutureQ1trFlag={1},FutureQ2trFlag={1},PYQtrFlag={1}>+<Year={2016}, Quarter }>}Yourdimension)

This can be your expression...

Which combines 2015 Q4, 2016 full year and 2017 Q1, Q2.

dandaanilreddy
Contributor III

Re: year and quarter

Thanks For the reply.

I tried but its not working. I am attaching the app on the main discussion. Please take a look

Thanks

Anil

Khan_Mohammed
Honored Contributor II

Re: year and quarter

Well I cannot reload the app as it is a SQL query.

Best is try what I've said in my latest reply ( I created flags) maybe that will work?

Thanks.

dandaanilreddy
Contributor III

Re: year and quarter

Hi

I tried your solution i created the fields in backend and in chart i wrote the the below expression but i am getting error.

Sum({<UnitActuals.FutureQ1trFlag={1},UnitActuals.FutureQ2trFlag={1},UnitActuals.PYQtrFlag={1}>+<UnitActuals.ReportedYear={2016}, UnitActuals.ReportedQuarter }>}UnitActuals.ReportedDate)

Thanks

Anil

Khan_Mohammed
Honored Contributor II

Re: year and quarter

I don't see any new fields for flags.

Note: My expression might be wrong, not an expert in Set Analysis but with those flags it will be very helpful for you to get what you want.

Upload latest qvw with all the flags loaded?

dandaanilreddy
Contributor III

Re: year and quarter

I have uploaded a new qvw with flags on main discussion. Please take a look.

Thanks

Anil