Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have this type of table in data model:
Period | Quarter | PeriodCounter |
---|---|---|
201701 | 2017-Q1 | 1 |
201702 | 2017-Q1 | 2 |
201703 | 2017-Q1 | 3 |
201704 | 2017-Q2 | 4 |
201705 | 2017-Q2 | 5 |
201706 | 2017-Q2 | 6 |
201707 | 2017-Q3 | 7 |
201708 | 2017-Q3 | 8 |
201709 | 2017-Q3 | 9 |
201710 | 2017-Q4 | 10 |
201711 | 2017-Q4 | 11 |
201712 | 2017-Q4 | 12 |
201801 | 2018-Q1 | 13 |
I load recno() as PeriodCounter. I would like to add another field QuarterCounter, but have no clue how to achieve that?
Load
Period,
Quarter,
recno() as PeriodCounter,
??? as QuarterCounter
Desired result:
Period | Quarter | PeriodCounter | QuarterCounter |
---|---|---|---|
201701 | 2017-Q1 | 1 | 1 |
201702 | 2017-Q1 | 2 | 1 |
201703 | 2017-Q1 | 3 | 1 |
201704 | 2017-Q2 | 4 | 2 |
201705 | 2017-Q2 | 5 | 2 |
201706 | 2017-Q2 | 6 | 2 |
201707 | 2017-Q3 | 7 | 3 |
201708 | 2017-Q3 | 8 | 3 |
201709 | 2017-Q3 | 9 | 3 |
201710 | 2017-Q4 | 10 | 4 |
201711 | 2017-Q4 | 11 | 4 |
201712 | 2017-Q4 | 12 | 4 |
201801 | 2018-Q1 | 13 | 5 |
Your help is greatly appreciated!
Two ways you can do this
1) AutoNumber(Quarter) as QuarterCounter
2) If(Quarter = Previous(Quarter), Peek('QuarterCounter'), RangeSum(Peek('QuarterCounter'), 1)) as QuarterCounter
For the second method to work without any flaws, make sure to sort the table in ascending order of Period
Try this?
Count(TOTAL <Quarterr> PeriodCounter)
If it is in script,
Table:
Load
Period,
Quarter,
recno() as PeriodCounter From Table;
Left Join (Table)
Load Quarter, Count(PeriodCounter) as QuarterCounter Group By Quarter Resident Table;
Two ways you can do this
1) AutoNumber(Quarter) as QuarterCounter
2) If(Quarter = Previous(Quarter), Peek('QuarterCounter'), RangeSum(Peek('QuarterCounter'), 1)) as QuarterCounter
For the second method to work without any flaws, make sure to sort the table in ascending order of Period
Thanks Anil!
Thanks Sunny, the first one looks very elegant!
It might look elegant, but might not give as good of as a performance as the second one. But since we are talking about calendar dimension (quarter), there might not be more than 1000 values in your calendar autonumber might not be too bad