topic Re: year and quarter in QlikView

year and quarter

dandaanilreddy — Wed, 25 Nov 2020 16:16:04 GMT

!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

Re: year and quarter

sangeess21 — Mon, 12 Dec 2016 20:19:35 GMT

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

Re: year and quarter

dandaanilreddy — Mon, 12 Dec 2016 20:21:02 GMT

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

Thanks

Anil

Re: year and quarter

MK9885 — Mon, 12 Dec 2016 20:47:15 GMT

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.

Re: year and quarter

MK9885 — Mon, 12 Dec 2016 21:00:52 GMT

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.

Re: year and quarter

dandaanilreddy — Mon, 12 Dec 2016 21:06:21 GMT

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

Re: year and quarter

MK9885 — Mon, 12 Dec 2016 21:09:40 GMT

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.

Re: year and quarter

dandaanilreddy — Mon, 12 Dec 2016 21:19:40 GMT

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

Re: year and quarter

MK9885 — Mon, 12 Dec 2016 22:02:00 GMT

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?

Re: year and quarter

dandaanilreddy — Mon, 12 Dec 2016 22:06:23 GMT

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

Thanks

Anil

Re: year and quarter

MK9885 — Mon, 12 Dec 2016 22:18:21 GMT

Thanks.

Where do you want to implement this expression and what output you expecting?

Any figures you've from db?

Re: year and quarter

dandaanilreddy — Mon, 12 Dec 2016 22:20:47 GMT

In the front end i have a chart in which i am displaying the quarters data for current year. My requirement is i want to show data from 2015 4th quarter to 2017 second quarter.

Thanks

Anil

Re: year and quarter

MK9885 — Mon, 12 Dec 2016 22:30:00 GMT

This can be your expression (I might be wrong)

=Count({<ProductGroup,UnitActuals.FutureQ1trFlag={1},UnitActuals.FutureQ2trFlag={1},UnitActuals.PYQtrFlag={1}>+<

UnitActuals.ReportedYear={2016}, UnitActuals.ReportedQuarter >}BillingRegionType )

=Count({<ProductGroup,UnitActuals.FutureQ1trFlag={1},UnitActuals.FutureQ2trFlag={1},UnitActuals.PYQtrFlag={1}>}BillingRegionType )+

Count({<UnitActuals.ReportedYear={2016}, UnitActuals.ReportedQuarter >}BillingRegionType )

I'm not sure if you want to use SUM or COUNT. Depends on your requirement again. With SUM it's giving 0 and with COUNT it's giving 6K...

I do not know what dimensions to consider but I've checked the Flags are working fine and giving you required quarters from 2017 and 2015 only.

stalwar1‌

jontydkpi‌

maxgro‌

awhitfield‌

Maybe someone with expertise level can help you in this?

Re: year and quarter

MK9885 — Mon, 12 Dec 2016 22:40:30 GMT

Here is what I could do.

Not sure if that is the output you looking for....