Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have a table containing 3 fields. i.e . dates ,items, quantity. i want that if i select a particular date for eg... 20/sept/2012. then ,, in table box i need that all the possibles values that culd have been for that date.. for eg.. if items field contained 3 items a,b,c.. and suppose in above mentioned date a item was cosumed.. then when i select that date i will get only a data in normal condition . but i want that when i select that date i get a, as well as b and c also but in place of b and c i will have null as quantity. and for a i will have actual quantity involved... please help me.. here is that excel file i have...
Thank you in advance.
you can use the calender object
something like this
put your date field into SaldoDatum
------------------------------
LET varMinDatum = num( MonthsStart(1,peek('SaldoDatumSort', 0, 'SaldenSort'),0)); // Erster des Monats nehmen
LET varMaxDatum = num(peek('SaldoDatumSort', -1, 'SaldenSort'));
//*************Temporäre Datumstabelle*************
Datumsfeld:
LOAD date($(varMinDatum)+IterNo()-1) AS Datumsfeld
AUTOGENERATE(1)
WHILE $(varMinDatum)+IterNo()-1<=$(varMaxDatum) ;
DROP Table SaldenSort;
//*************Kalendar Tabelle*************
Kalendar:
LOAD
Datumsfeld AS SaldoDatum,
Week(Datumsfeld) AS Woche,
Year(Datumsfeld) AS Jahr,
Month(Datumsfeld) AS Monat,
Day(Datumsfeld) AS Tag,
Weekday(Datumsfeld) AS Wochentag,
applymap('Quartale_Mapping', month(Datumsfeld), null()) AS Quartal,
date(monthstart(Datumsfeld),'MMM-YYYY') AS [Eindeutiger Monat],
Week(Datumsfeld)&'-'&year(Datumsfeld) AS [Eindeutige Woche],
Year2Date(Datumsfeld)*-1 AS YTDLaufendesJahr,
Year2Date(Datumsfeld,-1)*-1 AS YTDLetztesJahr
RESIDENT Datumsfeld;
DROP TABLE Datumsfeld;
Thank You for your answer. but i had tried this way before and it was not actually working..
Hi,
I didn't understand really what are you looking for. Someting like this?
see attached
ya..thank you so much.but i want same answer but both dimensions in y axis..
But the solution you have provided is in front end and the dimension items is taken on x-axis. but i need the same result if both the dimensions are in x-axis and from back-end.
Calculate your expression from backend with group by required dimentions.
And then you can write if condition like if(IsNull(CalcuatedExpression),null(),CalcuatedExpression))
But I am not geeting that result in this way also. i did group by. but it shows null in this case only if all the three items are not available for a particular date.if any one is there then it doesnt show nulls for remaining two.
Have a look at the sample here suggested by John Witherspoon :