Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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..
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.
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.
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..
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.