Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a date field (Salesweek) and few other KPI's. I created listbox for Salesweek and other table chart has KPI's.
When I select Salesweek 8/18/2014 in the list box and my table charts has to show NET KPI for that selected date and a week before data 8/11/2014.
Prod NET NETpreviousweek
LAPTOP $2000 $2500
Is this can be achieved by set expression?
Thanks,
Selected week: Sum({<Salesweek={$}>}Sales)
Week before: Sum({<Salesweek={$(=Date(Max(Salesweek)-7,'M/D/YYYY'))}>}Sales)
Thanks for your quick reply, but its not working
Thanks,
Is Saleweek field has same value 8/18/2014 for entire week 8/11/2014 to 8/18/2014?
Can you show sample data?
Hi Madhu,
Hope this helps.
Regards
KC
Its having week starting date like 7/28/2014, 8/4/2014, 8/11/2014,8/18/2014 like that...
Jyothish, I am using personal edition.
Thanks,
Hi madhu,
create a variable:
Var=GetFieldSelections(Date)
exp1:
sum({<Date={"$(=Var)"}>}Amount)
Exp2:
sum({<Date={">=$(=Var)<=$(=Var)-7"}>}Amount)
In dimension:
Product
Rename the fields as per ur data.
Regards
KC
Selected week: Sum(Sales)
EDIT:
Previous week: Sum(TOTAL {<Saleweek={"$(=Date(Max(Saleweek)-7,'M/D/YYYY'))"}>}Sales)
I guess second expression needs some changes as it has to sum amount for previous week:
sum({<Date={"<=$(=Var)>=$(=Var)-7"}>}Amount)
But it still ignoring Var-7.
Thanks,
sum( TOTAL {<Saleweek={">=$(=Date($(=Var-7),'M/D/YYYY'))<$(=Var)"}>}Sales)