year and quarter

[dandaanilreddy]

!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

[sangeess21]

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]

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

Thanks

Anil

[MK9885]

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.

[MK9885]

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]

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

[MK9885]

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]

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

[MK9885]

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]

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

Thanks

Anil