topic Time comparison in QlikView

Time comparison

narayanamsn — Thu, 19 Apr 2018 16:59:18 GMT

@@Hi Gurus,

I have an example with dummy sales data where in i need to plot time series Quarterly sales comparison.

I want to derive comparison table that holds the sales with max quarters for each combination.

This table should change the values if I select any Quarter accordingly

Company	Route	Max Qtr	Pre Qtr	Same Qtr Last Yr	Q/Q	Y/Y
A	Retail	180	-	300	=(180 -0 )/0	=(180 -300 )/300
A	wholesale	200	150	290	=(200 - 150 )/150	=(200 - 290 )/290
	Total	380	150	590	=(380 - 150 )/150	=(380 - 590 )/590
B	Retail	120	-	150	=(120 - 0 )/0	=(120 - 150 )/150
B	wholesale	200	150	180	=(200 - 150 )/150	=(200 - 180 )/180
	Total	320	150	330	=(320-150)/150	=(320 - 330)/330

Re: Time comparison

vishsaggi — Thu, 19 Apr 2018 18:54:32 GMT

Can you tell me Max Qtr for Retail and Wholesale is 180 and 200? I mean which year? Do you select in year too? Is the above sample data you posted is your expected data?

Re: Time comparison

narayanamsn — Thu, 19 Apr 2018 19:02:20 GMT

Hi Vishwarath,

you are correct. Max Qtr is for Max Year. I am expecting the same table that i added

Re: Time comparison

sunny_talwar — Thu, 19 Apr 2018 19:21:03 GMT

What you have listed above is the expected output you are looking to get?

Re: Time comparison

sunny_talwar — Thu, 19 Apr 2018 19:29:23 GMT

This?

Re: Time comparison

narayanamsn — Thu, 19 Apr 2018 19:45:33 GMT

Yes Sunny

Re: Time comparison

narayanamsn — Thu, 19 Apr 2018 19:46:57 GMT

will you be able to share the QVW?

Re: Time comparison

sunny_talwar — Thu, 19 Apr 2018 19:47:04 GMT

Great, I have attached the qvw to my response above... you can see how to do it

Re: Time comparison

sunny_talwar — Thu, 19 Apr 2018 19:47:24 GMT

Open this link for th qvw

Re: Time comparison

vishsaggi — Thu, 19 Apr 2018 20:01:11 GMT

Sunny Just wondering can we also write like this?

= Max({<Qtr, FYQTR, YearMonth = {"$(=Max(YearMonth,2))"}>}Sales)
//Sum({<YearMonth = {"$(=Max({<Qtr, FYQTR, YearMonth = {[$(='<=' & Max(YearMonth))]}>}YearMonth, 2))"}, Qtr, FYQTR>}Sales)

I get the same results though wanted to know the usage of YearMonth = {[$(='<=' & Max(YearMonth))]}>}?

Re: Time comparison

sunny_talwar — Thu, 19 Apr 2018 20:05:03 GMT

‌Make a selection in FYQTR and you will get your answer.

Re: Time comparison

narayanamsn — Thu, 19 Apr 2018 20:07:38 GMT

Thank you Sunny,

I got 2 queries.

YearMonth = {[$(='<=' & Max(YearMonth))]}>}YearMonth, 2))"}

1. Can I write the above condition like this YearMonth = {"$(= Max(YearMonth ,2))"}..

What is the difference between the above 2 conditions?

2. I am unable to get the % if I use the actual expressions instead of =Column(1)/Column(2)-1?

what could be the reason for not getting the same result if I use expression instead of column()?

Regards,

Satya

Re: Time comparison

narayanamsn — Thu, 19 Apr 2018 20:19:43 GMT

= Sum({<Qtr, FYQTR, YearMonth = {"$(=Max(YearMonth,2))"}>}Sales) will make the values to 0 when we select any qtr

Re: Time comparison

vishsaggi — Thu, 19 Apr 2018 20:31:15 GMT

Got it.

Re: Time comparison

sunny_talwar — Thu, 19 Apr 2018 20:54:30 GMT

1) Nope, one is saying <= second max month year and other is just = to second max month year

2) I don't see why you can't do it... I tried and works the same way

Re: Time comparison

sunny_talwar — Thu, 19 Apr 2018 20:54:46 GMT

Yup, you got it

Re: Time comparison

sunny_talwar — Thu, 19 Apr 2018 20:54:56 GMT

And glad you got it too