Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Given below is the screenshot what I want to do in Qlik. Subtracting SOH of 1-Jan 2015 from 1-Jan 2016. Please help how can I accomplish this.
Can you provide an example of you data ? or is it laid out like en the excel file ?
Hi Shivam,
If you need compare only two years, i would implement this in load script.
First solution, you have two tables, and the calculations are done at chart functions:
PrevYear:
Load
Month & Week as Key
,Day
,SKU
,SOH as SOH_prevYear
from [YourFile]
where year(Date) = [previous year];
ActYear:
Load
Month & Week as Key
,Day
,SKU
,SOH as SOH_actYear
from [YourFile]
where year(Date) = [actual year]
Second solution, you have one output table, and the calculations are done at chart functions, or you can do at load script as well:
YourTable:
Load
Month & Week as Key
,SKU
,SOH as SOH_prevYear
from [YourFile]
where year(Date) = [previous year];
Left join(YourTable)
Load
Month & Week as Key
,Day
,SKU
,SOH as SOH_actYear
from [YourFile]
where year(Date) = [actual year];
//======================================
It would be better to know your exact tables, but the logic is same.
I think it would it possible with set analysis too.
G.
Hi,
Please try use set analysis and find out Max (Year) -1 based on current selection. Hope it helps
Thanks
Hi Ramon, This is the screen of my data it starts from 31-12-2012 and continues till current date. So I have to calculate (31-12-2013) Units - (31-12-2012) and so on till current date.
Hi Undergrinder, No I don't have to compare just of 2 years. I've attached a screenshot my data in the above comment. My data starts from 31-12-2012 to Current Date.
Hi Srinivasa, Can u provide me an example on how to use this in my case. I've attached a screenshot of my data in the above comments.
hi Shivam,
do you want like this?
Yes like this only but I was wondering is it possible for me to create a measure which will subtract 1 years SOH from the dates I select.
Hi Shivam,
If you have a master calender which has flags for this year, last year, and year prior to that, your calculations could be made easier using these flags in set analysis.
Make use of inYear() or inYearToDate()(Which ever one is more approprate) function .