Comparision

[#1]

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.

[tresesco]

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.

[#3]

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

[#4]

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.

[#5 · Accepted solution]

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.

[#6]

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

[#7]

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.