topic Re: Comparision in QlikView

Comparision

Tue, 16 Jul 2013 03:18:37 GMT

Hi Experts,

I have a query. Guide me how I can implement it in Qlikview.

Table:

ID Date Amount

A 1/2/2013 100

The above table is dynamic in nature. Everyday we can see only one row updated. There is no history maintained. Now I want to compare current amount Vs Last week amount.

For Example:

Amount on 1/2/2013 Vs Amount on 23/1/2013.

So everyday I want to compare current Vs Last week amount. How to implement it. Please suggest me.

Re: Comparision

tresB — Tue, 16 Jul 2013 04:04:31 GMT

If history is not maintained in the source, you can(rather have to) maintain it in the qvd. Store the historical data into a qvd and then compare the data.

Re: Comparision

Tue, 16 Jul 2013 04:35:38 GMT

Thanks for your reply.

I want to store only 2 rows always. If I store it in qvd and compare then I wil be having lot of rows. So hot to have always two rows in the data.

For Exp:

Assume that I have data like below..

ID Date amount

A 9/7/2013 100

After implementing the technique I should have every day 2 rows..

Like below

ID Date amount

A 9/7/2013 100

A 16/7/2013 200

Again next day

ID Date amount

A 10/7/2013 150

A 17/7/2013 300

It should go on....Hope it clears the problem.Can you give piece of code..

Re: Comparision

Tue, 16 Jul 2013 05:33:48 GMT

Hi Suneel,

Always you can store last seven days data in the Qvd. so that you can compare todays data with the first day data. if you are loading the todays data you can keep only the last 7 day's data and delete the 8th day data.

for example:

if you are loading on 16/7/2013. keep data from 9/7/2013 to 16/7/2013 in Qvd

if you are loading ob 17/7/2013 keep data from 8/7/2013 to 17/7/2013 in Qvd

Take the firstday and lastday data and compare them.

Re: Comparision

Tue, 16 Jul 2013 06:50:09 GMT

U need to write a script like below.

Tbl:

Load ID

,Date

,Amount

From Yourtable.qvd where Date>=date(today()-7);

Concatenate

Sql Select * from Your_table;

Store into table Yourtable.qvd;

Comparision in chart.

Use the below expression:

Sum({1 <Date={$(=Today())}>} Amount)// Current day Amount

Sum({1 <Date={$(=Today()-7)}>} Amount) // Before 7 day

Regards,

Kabilan K.

Re: Comparision

Tue, 16 Jul 2013 08:06:22 GMT

Thanks for your Reply.

Just one more small doubt.. At the very first time there will be no QVD generated . I think it will give error since we do not have qvd generated at first time. Hopefully

need to use condition of whether qvd exists or not.

Let me know your idea..

Re: Comparision

Tue, 16 Jul 2013 09:49:37 GMT

Yes, Just create it manually first and use it.

After that no need to check Whether qvd exsits or not, I think.

How ever it will be there, so no need to check.

Regards,

Kabilan K.