Hi, sorry to trouble you guys again. :) :)
I'm using Singleton to populate some of the data in this table. The data is coming from different sources so I'm trying to have two different Queries.
One to populate the total cars sold for FY 2018 and the previous year and another Query for Total Cars sent to Junk Yard for 2018 and the previous year.
This way, I can drop the Data Items to the Singleton that looks like the attached mockup.
When I try a Case statement such as below, I get '0'. The [Car Total Count] is a Measure.
Data Item: Going back one year.
Case
when ([Car Sold Date] <= _add_days(?Prompt Date?,-365) then
([Car Total Count])
else
(0)
end
Any ideas what's causing it to drop a "0"
Your post does not have enough information to analyse the problem.
The [Car Total Count] is supposed to be for Cars sold or cars sent to junkyard? -365 does not necessarily mean last year.
If the date of the transaction does not fall into your calculation then it will produce 0 so that's ok which might be the reason yuo are seeing 0.
If you want a report like your screenshot shows, then you could simply create two queries, one for Cars Sold and another for Cars sent to junkyard (presuming they are from two data sources) and then union them to produce the output.
My apologies. My questions is,
Just talking about Cars Sold, I'm trying to stay with one Query and not two but have two columns for years (2018 and 2017) in the Crosstab.
So, basically, when I create two different Data Items using the below code, I get zero on the 2017 going back one year.
For: 2017:
Case
when ([Car Sold Date] <= _add_days(?Prompt Date?,-365) then
([Car Total Count])
else
(0)
end
Ignore the Cards junked for now. Sure, I understand one year may not be 365.
Thanks so much@
Can't answer without knowing more about your package and joins between the fact table and the date dim.
Moreover, you should replace the calculation with something like this:
Case
When extract(year,[Car Sold Date]) = extract(year,current_date) -1 Then [Car Total Count]
Else 0
End
Yes, thank you so much. This did the trick. :) :)