topic Re: Comparison of Data ?? in QlikView

Comparison of Data ??

sujeetsingh — Thu, 18 Feb 2016 10:03:27 GMT

Hello all,

Please find the attached sample and it to has details of the problem.

Thanks,

Re: Comparison of Data ??

tresB — Thu, 18 Feb 2016 10:24:06 GMT

Tab1:

Load *

INLINE [

    ID, Year, "Quarter Year", Sales

    1, 2011, Q1-2011, 1

    2, 2011, Q2-2011, 3

    3, 2011, Q3-2011, 4

    4, 2011, Q4-2011, 1

    5, 2012, Q1-2012, 5

    6, 2012, Q2-2012, 12

    7, 2012, Q3-2012, 13

    8, 2012, Q4-2012, 14

    9, 2013, Q1-2013, 21

    10, 2013, Q2-2013, 18

];

left Join

Load

'Q'&Mid([Quarter Year],2,1)&'-'&(Num(Mid([Quarter Year],4,4))+1) as [Quarter Year],

Sales as PrioSales

Resident Tab1;

Re: Comparison of Data ??

Anonymous — Thu, 18 Feb 2016 10:38:11 GMT

There is no vaules in prioir of the 2011

Re: Comparison of Data ??

sujeetsingh — Thu, 18 Feb 2016 10:46:45 GMT

Tresco I need the total sum of Year to ploted against each quarter of the next year.

That is waht I calculated PreSales. I want to achive it at UI.

Re: Comparison of Data ??

kaushiknsolanki — Thu, 18 Feb 2016 10:51:26 GMT

Dear Sujeet,

Its always better to move the calculations at the script level.

I would recommended that you should try same logic as the example tresesco‌ had given.

Regards,
Kaushik Solanki

Re: Comparison of Data ??

sujeetsingh — Thu, 18 Feb 2016 10:56:19 GMT

I agree but a complex data models hinders all here.

Re: Comparison of Data ??

kaushiknsolanki — Thu, 18 Feb 2016 10:59:13 GMT

Sujeet I agree too that it wont be as simple as it seems, but as a developer you should also think about end users performance.

Keeping heaving calculations like this, may kill your application when you will have huge data.

So try if you can accommodate the logic in script to reduce the performance.

Regards,

Kaushik Solanki

Re: Comparison of Data ??

tresB — Thu, 18 Feb 2016 11:16:43 GMT

That can be done using a synthetic dimension and expression in combination with Pick() and Match(). That would be complex and with some disadvantages.

Re: Comparison of Data ??

jagan — Fri, 19 Feb 2016 03:01:57 GMT

Hi Sujeet,

It is difficult to handle this in Front end because we cannot control the axis, show data for one quarter in another quarter in chart. It should be handled in Script only.

If there is a fixed number of quarters to display in the chart then we can do this easily by using synthetic dimension since it has no relation to the expression

Hope this helps you.

Regards,

Jagan.

Re: Comparison of Data ??

jagan — Fri, 19 Feb 2016 03:10:41 GMT

Hi,

Try this script

Tab1:

Load

ID,

Year,

[Quarter Year], Sales

INLINE [

ID, Year, "Quarter Year", Sales

1, 2011, Q1-2011, 1

2, 2011, Q2-2011, 3

3, 2011, Q3-2011, 4

4, 2011, Q4-2011, 1

5, 2012, Q1-2012, 5

6, 2012, Q2-2012, 12

7, 2012, Q3-2012, 13

8, 2012, Q4-2012, 14

9, 2013, Q1-2013, 21

10, 2013, Q2-2013, 18

];

YearlySales:

Load

(Year + 1) as Year,

Sum(Sales) as PrioSales

Resident Tab1

WHERE Exists(Year, Year +1)

Group BY Year

Re: Comparison of Data ??

sujeetsingh — Fri, 19 Feb 2016 04:30:05 GMT

I discovered a way in UI. The trick is to use aggr function on Year and use Above in next expression. Thanks all specially jagan‌ tresesco‌ . Thanks all.