Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have a Pivot table BG looking like below showing JUN'14 data:
When I put the below condition in BG:
if(TimePeriod='MTD',Sum({<[Transaction Type]=>}[Gross Budget]),
Sum ({<YrMonth = {">=$(=yearstart(Date(VDateMax),0,1)) <=$(=Date(VDateMax))"},MonthName=,[Transaction Type]=>}[Gross Budget]))
Transaction Type is only applicable for Actuals, so I had to Ignore it in Budget expression above through Set Analysis.
It gives me an extra product METACAM CATTLE which is 1090000 for MTD which is actually for MAY and not present for Jun, however YTD is correct since no values for this product in earlier months.
I have kept 2nd chart MTD for reference which has 2 normal expressions for MTD and YTD and is showing correct data for the same product.
I hope you guys will be able to understand the above explaination. If not please let me know, I will try to further detail it.
Thanks in advance!
Neelam
Hi Jagan,
Thanks for your efforts.
Your suggestion didn't work, but I started from it & was able to get to the solution. What I did is:
Budget:
LOAD MonthName,
[Product Family],
Budget,
'Normal' as [Transaction Type],
Num(YrMonth) as YrMonth,
YrMonth as Date
FROM Budget.xls (biff, embedded labels, table is [Sheet1$]);
Concatenate(Budget)
Load 'Returns' as [Transaction Type],
MonthName,
null() as Budget,
[Product Family],
Num(YrMonth) as YrMonth,
YrMonth as Date resident Budget;
Concatenate(Budget)
LOAD MonthName,
[Product Family],
[Transaction Type],
Actuals,
Num(YrMonth) as YrMonth,
YrMonth as Date
FROM Actuals.xls (biff, embedded labels, table is [Sheet1$]);
Concatenating all the Transaction Types with Budget as Null helped.
Also, the expression would be now:
if(TimePeriod='MTD',
Sum({<[Transaction Type]={'Normal'}>}Budget),
Sum({<YrMonth = {">=$(=(Var1)) <=$(=(Var2))"},MonthName=,[Transaction Type]={'Normal'}>} Budget)
)
Thanks a lot for this idea!
Regards,
Neelam
Hi,
Said that, you have explained it nicely, expression seems good. Could you reduce your app and create a sample qvw(removing other conf info) and share here so that we can have a look and might find a reason which is not visible here in screen shot?
Hi Neelam,
Try this expression
if(TimePeriod='MTD',
Sum({<Date={'$(=Date(Max(Date)))'}, [Transaction Type]=>}Budget),
Sum({<Date={'$(=Date(Max(Date)))'}, YrMonth = {">=$(=(Var1)) <=$(=(Var2))"},MonthName=,[Transaction Type]=>} Budget)
)
Regards,
Jagan.
Hi Jagan,
It didn't work
I have attached a sample QVW for the same issue.
Thanks for the help.
Neelam
Hi,
Please find attached file for solution.
Regards,
Jagan.
Hi Jagan,
Thanks for your efforts!
I saw you had put Date={'$(=Date(Max(Date)))'} into YTD formula also and it was giving monthly values due to this.
When I removed this from YTD to get YTD values it again started giving the value 1090 in MTD for JUNE for METACAM CATTLE which should not come.
Regards,
Neelam
Hi Neelam,
The Transaction Type filter causing the issue, if you remove the selections and use below expression it works as expected
if(TimePeriod='MTD',
Sum({<Date={'$(=Date(Max(Date)))'}, [Transaction Type]=>}Budget),
Sum({<YrMonth = {">=$(=(Var1)) <=$(=(Var2))"},MonthName=, [Transaction Type]=>} Budget)
)
I think it is better to remove that filter, since you are not using it.
Regards,
jagan.
Hi jagan,
I understand that filter is causing the issue. Actually this filter is not applicable to Budget, but important for Actuals. And Budget & Actuals are present together in the same chart, so it needs to be ignored in Budget.
Without Transaction Type Selection, everything is coming correctly.
Hence I was trying to get it done this way.
Thanks & Regards,
Neelam
Hi,
In script try like this
Sales:
LOAD MonthName,
[Product Family],
[Transaction Type],
Actuals,
Num(YrMonth) as YrMonth,
YrMonth as Date
FROM
Actuals.xls
(biff, embedded labels, table is [Sheet1$]);
Concatenate
LOAD MonthName,
[Product Family],
Budget,
Null() AS [Transaction Type],
Num(YrMonth) as YrMonth,
YrMonth as Date
FROM
Budget.xls
(biff, embedded labels, table is [Sheet1$]);
TimePeriod_TH:
Load * inline [ TimePeriod
MTD
YTD
];
Now use this expression in chart
if(TimePeriod='MTD',
Sum({<Date={'$(=Date(Max(Date)))'}, [Transaction Type]-={'*'}>}Budget),
Sum({<YrMonth = {">=$(=(Var1)) <=$(=(Var2))"},MonthName=,[Transaction Type]-={'*'}>} Budget)
)
Or
if(TimePeriod='MTD',
Sum({<Date={'$(=Date(Max(Date)))'}, [Transaction Type]=>}Budget),
Sum({<YrMonth = {">=$(=(Var1)) <=$(=(Var2))"},MonthName=,[Transaction Type]=>} Budget)
)
Hope this helps you.
Regards,
jagan.
Hi Jagan,
Thanks for your efforts.
Your suggestion didn't work, but I started from it & was able to get to the solution. What I did is:
Budget:
LOAD MonthName,
[Product Family],
Budget,
'Normal' as [Transaction Type],
Num(YrMonth) as YrMonth,
YrMonth as Date
FROM Budget.xls (biff, embedded labels, table is [Sheet1$]);
Concatenate(Budget)
Load 'Returns' as [Transaction Type],
MonthName,
null() as Budget,
[Product Family],
Num(YrMonth) as YrMonth,
YrMonth as Date resident Budget;
Concatenate(Budget)
LOAD MonthName,
[Product Family],
[Transaction Type],
Actuals,
Num(YrMonth) as YrMonth,
YrMonth as Date
FROM Actuals.xls (biff, embedded labels, table is [Sheet1$]);
Concatenating all the Transaction Types with Budget as Null helped.
Also, the expression would be now:
if(TimePeriod='MTD',
Sum({<[Transaction Type]={'Normal'}>}Budget),
Sum({<YrMonth = {">=$(=(Var1)) <=$(=(Var2))"},MonthName=,[Transaction Type]={'Normal'}>} Budget)
)
Thanks a lot for this idea!
Regards,
Neelam